Truy vấn Dữ liệu Quản lý Dịch bệnh COVID-19 Bằng MySQL

Truy vấn các địa điểm có tổng lưu lượng người lớn hơn 30, hiển thị tên địa điểm và tổng lưu lượng người (dùng tiêu đề là visitors). Kết quả được sắp xếp theo lưu lượng người từ cao đến thấp, nếu lưu lượng bằng nhau thì sắp xếp theo tên địa điểm.

SELECT location_name, COUNT(*) AS visitors 
FROM location JOIN itinerary ON location.id = itinerary.loc_id
GROUP BY loc_id
HAVING visitors > 30
ORDER BY visitors DESC, location_name;

Truy vấn số lượng người đang cách ly tại mỗi địa điểm cách ly, hiển thị tên địa điểm và số lượng người (dùng tiêu đề là number). Kết quả được sắp xếp theo số lượng người từ nhiều đến ít, nếu số lượng bằng nhau thì sắp xếp theo tên địa điểm.

SELECT location_name, COUNT(*) AS number 
FROM isolation_record JOIN isolation_location ON isolation_record.isol_loc_id = isolation_location.id
WHERE state = 1
GROUP BY isolation_location.id
ORDER BY number DESC, location_name;

Truy vấn thông tin về các chuyến đi liên tiếp của cùng một người trong bảng lịch trình. Kết quả bao gồm: ID người, họ tên, thời gian trùng, ID địa điểm bắt đầu, tên địa điểm bắt đầu, ID địa điểm kết thúc, tên địa điểm kết thúc. Kết quả được sắp xếp theo ID người.

SELECT p.id, fullname, telephone, i1.e_time AS reclosing_time, i1.loc_id AS loc1, l1.location_name AS address1, i2.loc_id AS loc2, l2.location_name AS address2
FROM itinerary i1 JOIN itinerary i2 ON i1.e_time = i2.s_time AND i1.p_id = i2.p_id
JOIN location l1 ON i1.loc_id = l1.id
JOIN location l2 ON i2.loc_id = l2.id
JOIN person p ON p.id = i1.p_id
WHERE p.id > 30
ORDER BY p.id, reclosing_time;

Truy vấn lịch trình của Cung Minh Dao và Jia Han Shan, bao gồm: họ tên, số điện thoại, nơi đã đến (tên địa điểm), thời gian đến, thời gian rời đi. Kết quả được sắp xếp theo ID người giảm dần, sau đó là thời gian bắt đầu.

SELECT fullname, telephone, location_name, s_time, e_time
FROM person
LEFT JOIN itinerary ON person.id = itinerary.p_id
LEFT JOIN location ON location.id = itinerary.loc_id
WHERE fullname IN ('Cung Minh Dao', 'Jia Han Shan')
ORDER BY person.id DESC, s_time;

Truy vấn ID và tên của các địa điểm có chữ "店" trong tên. Kết quả được sắp xếp theo ID địa điểm.

SELECT id, location_name FROM location
WHERE location_name LIKE '%店%'
ORDER BY id;

Truy vấn danh sách tên và số điện thoại của những người đã tiếp xúc với một người nhiễm bệnh mới được phát hiện, người này đã ở "活动中心" từ 20:05:40 đến 21:25:40 ngày 2/2/2021. Kết quả được sắp xếp theo tên.

SELECT DISTINCT fullname, telephone
FROM itinerary
JOIN person ON person.id = itinerary.p_id
JOIN location ON location.id = itinerary.loc_id
WHERE location_name = '活动中心'
AND s_time < '2021-02-02 21:25:40'
AND e_time > '2021-02-02 20:05:40'
ORDER BY fullname;

Truy vấn tên của các địa điểm cách ly đang được sử dụng, sắp xếp theo ID địa điểm.

SELECT location_name
FROM (
    SELECT DISTINCT isolation_location.id, location_name
    FROM isolation_location
    JOIN isolation_record ON isolation_location.id = isolation_record.isol_loc_id
    WHERE state = 1
    ORDER BY isolation_location.id
) q;

Truy vấn tên và số điện thoại của 30 người đầu tiên có lịch trình di chuyển, sắp xếp theo ID người.

SELECT fullname, telephone
FROM person
WHERE EXISTS (
    SELECT * FROM itinerary
    WHERE person.id <= 31 AND p_id = person.id
)
ORDER BY person.id;

Truy vấn số lượng người chưa từng đến "Today便利店", đặt tên cho số lượng này là number.

SELECT COUNT(*) AS number
FROM person
WHERE NOT EXISTS (
    SELECT * FROM location
    JOIN itinerary ON location.id = itinerary.loc_id
    WHERE location_name = 'Today便利店' AND itinerary.p_id = person.id
);

Truy vấn tên của những người đã đến tất cả các địa điểm, sắp xếp theo tên.

SELECT fullname
FROM person
WHERE NOT EXISTS (
    SELECT * FROM location
    WHERE location.id NOT IN (
        SELECT loc_id FROM itinerary
        WHERE itinerary.p_id = person.id
    )
)
ORDER BY fullname;

Tạo view isolation_location_status để phản ánh tình trạng hiện tại của tất cả các điểm cách ly, bao gồm: ID địa điểm, tên địa điểm, dung lượng phòng, số lượng phòng đã sử dụng (đặt tên là occupied).

CREATE VIEW isolation_location_status AS
SELECT 
    isolation_location.id,
    isolation_location.location_name,
    isolation_location.capacity,
    COUNT(CASE WHEN isolation_record.state = 1 THEN 1 ELSE NULL END) AS occupied
FROM 
    isolation_location
JOIN 
    isolation_record ON isolation_record.isol_loc_id = isolation_location.id
GROUP BY 
    isolation_location.id, 
    isolation_location.location_name,
    isolation_location.capacity;

Truy vấn số lượng phòng trống còn lại tại các điểm cách ly từ view isolation_location_status, sắp xếp theo ID địa điểm.

SELECT location_name, capacity - occupied AS available_rooms
FROM isolation_location_status
ORDER BY id;

Truy vấn danh sách tên và số điện thoại của những người đã tiếp xúc với Jin Wan Er, sắp xếp theo tên.

SELECT fullname, telephone
FROM itinerary
JOIN person ON person.id = itinerary.p_id
JOIN (
    SELECT loc_id, s_time, e_time
    FROM itinerary
    JOIN person ON person.id = itinerary.p_id
    WHERE fullname = 'Jin Wan Er'
) AS scl ON itinerary.loc_id = scl.loc_id
WHERE itinerary.s_time < scl.e_time
AND itinerary.e_time > scl.s_time
AND fullname != 'Jin Wan Er'
ORDER BY fullname;

Truy vấn số lượng người tiếp xúc gần gũi tại mỗi địa điểm, sắp xếp theo số lượng người tiếp xúc từ nhiều đến ít, nếu bằng nhau thì sắp xếp theo tên địa điểm.

SELECT location_name, COUNT(*) AS close_contact_number
FROM location
JOIN close_contact ON location.id = close_contact.loc_id
GROUP BY location_name
ORDER BY close_contact_number DESC, location_name;

Truy vấn ID, tên và số lượng người bị lây nhiễm bởi người có số lượng lây nhiễm cao nhất, đặt tên cho số lượng này là infected_number.

SELECT case_p_id, fullname, COUNT(*) AS infected_number
FROM person
JOIN close_contact ON person.id = close_contact.case_p_id
GROUP BY case_p_id, fullname
ORDER BY infected_number DESC
LIMIT 1;

Truy vấn 3 người có lịch trình di chuyển nhiều nhất trong khoảng thời gian từ 10:00:00 đến 14:00:00 ngày 2/2/2021, sắp xếp theo số lượng lịch trình, nếu bằng nhau thì sắp xếp theo tên.

SELECT fullname, COUNT(*) AS record_number
FROM person
JOIN itinerary ON person.id = itinerary.p_id
WHERE s_time <= '2021-02-02 14:00:00'
AND e_time >= '2021-02-02 10:00:00'
GROUP BY fullname
ORDER BY record_number DESC, fullname
LIMIT 3;

Truy vấn tên và số lượng phòng của điểm cách ly có số lượng phòng thứ hai nhiều nhất.

SELECT location_name, capacity
FROM isolation_location
WHERE capacity < (
    SELECT MAX(capacity) FROM isolation_location
)
ORDER BY capacity DESC
LIMIT 1;

Thẻ: mysql sql 数据查询数据库管理疫情管理

Đăng vào ngày 16 tháng 7 lúc 15:24

Thành phố Cuồng loạn

Truy vấn Dữ liệu Quản lý Dịch bệnh COVID-19 Bằng MySQL

Thẻ Phổ Biến