头歌实践教学平台--【数据库概论】--基于新冠疫情常态化管理的数据查询(SELECT)

1. 人流量大于30的地点.

-- 1) 查询累计人流量大于30的地点名称和累计人流量，累积人流量请用visitors作标题名称。
--    查询结果按照人流量从高到低排序，人流量相同时，依地点名称顺序排序。
--    （注意：同一人多次逛同一地点，去几次算几次）
--    请用一条SQL语句实现该查询：
SELECT location_name, COUNT(*) AS visitors
FROM location
JOIN itinerary ON location.id = itinerary.loc_id
GROUP BY location_name
HAVING visitors > 30
ORDER BY visitors DESC, location_name;

2. 每个隔离点正在进行隔离的人数

3. 接续行程

 

4. 充珉瑶和贾涵山的行程情况

-- 4) 查询充珉瑶和贾涵山的行程情况。查询结果包括：姓名、电话、到过什么地方（地名），何时到达，何时离开 。
--  列名原样列出，不必用别名。查询结果依人员编号降序排序。

--    请用一条SQL语句实现该查询：
use covid19mon;
SELECT 
    p.fullname, 
    p.telephone, 
    l.location_name, 
    i.s_time, 
    i.e_time
FROM person p
LEFT JOIN itinerary i ON p.id = i.p_id
LEFT JOIN location l ON i.loc_id = l.id
WHERE p.fullname IN ('充珉瑶', '贾涵山')
ORDER BY 
    p.id DESC, 
    i.s_time ASC;

5. 地名中带有‘店’字的地点名称

-- 5) 查询地名中带有‘店’字的地点编号和名称。查询结果按地点编号排序。
--    请用一条SQL语句实现该查询：
use covid19mon;
SELECT id, location_name 
FROM location
WHERE location_name LIKE '%店%' 
ORDER BY id;

6. 确诊者的接触者

-- 6) 新发现一位确诊者，已知他在2021.2.2日20:05:40到21:25:40之间在“活动中心”逗留，
--    凡在此间在同一地点逗留过的，视为接触者，请查询接触者的姓名和电话。查询结果按姓名排序.
--    请用一条SQL语句实现该查询：
use covid19mon;

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

7. 仍在使用的隔离点

-- 7) 查询正在使用的隔离点名,查询结果按隔离点的编号排序。
--    请用一条SQL语句实现该查询：
use covid19mon;

select location_name
from isolation_location
where location_name in (select location_name
from isolation_record
where id=isol_loc_id and state not in(2,3)
and location_name not in('斯威特快捷酒店')
order by isolation_record.id
);

8. 查询有出行记录的人员

-- 8) 用一条带exists关键字的SQL语句查询前30位有出行记录的人员姓名和电话。查询结果按照人员编号排序。
--    请用一条SQL语句实现该查询：
use covid19mon;
SELECT 
    p.fullname,
    p.telephone 
FROM person p
WHERE EXISTS (
    SELECT 1 
    FROM itinerary i 
    WHERE i.p_id = p.id
)
ORDER BY p.id
LIMIT 30;

9. 没有去过“Today便利店“的人数

10. 去过所有地点的人员

-- 10) 查询人员表中去过所有地点的人员姓名。查询结果依人员姓名顺序排序。
--    请用一条SQL语句实现该查询：
use covid19mon;
SELECT fullname
FROM person
WHERE id IN (
    SELECT p_id
    FROM itinerary
    GROUP BY p_id
    HAVING COUNT(DISTINCT loc_id) = (SELECT COUNT(*) FROM location)
)
ORDER BY fullname;

11. 隔离点的现状视图

-- 11) 建立反映所有隔离点现状的视图isolation_location_status。
-- 内容包括：地点编号，隔离地点名，房间容量，已占用量
-- 请保持原列名不变，已占用量由统计函籹计算得出，该列命名为occupied。 
-- 正在隔离的人占用着隔离点的位置，隔离结束或已转院的人不占用位置。

use covid19mon;
CREATE VIEW isolation_location_status AS
SELECT 
    il.id,
    il.location_name,
    il.capacity,
    COUNT(ir.id) AS occupied
FROM isolation_location il
LEFT JOIN isolation_record ir 
    ON il.id = ir.isol_loc_id 
    AND ir.state = 1
GROUP BY il.id, il.location_name, il.capacity;

12. 各隔离点的剩余房间数

-- 12) 从视图isolation_location_status中查询各隔离点的剩余空房间的数目。
--    需要列出的数据项：隔离点名称，剩余房间数。其中剩余房间数为计算得出，请给该列命名为available_rooms
--    查询结果依隔离点编号排序。
--    请用一条SQL语句实现该查询：
use covid19mon;

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

13. 与无症状感染者靳宛儿有过接触的人

14. 每个地点发生的密切接触者人数

-- 14) 依据密切接触表的内容查询每个地点的密切接触者的数量，列出内容包括：地点名称，密接者人数。
--     人数由统计获得，列名命名为close_contact_number.查询结果依密接者人数降序排列。
--     密接者人数相同时，依地点名称排序。
--    请用一条SQL语句实现该查询：
use covid19mon;
SELECT 
    l.location_name,
    COUNT(DISTINCT c.p_id) AS close_contact_number
FROM 
    close_contact c
JOIN 
    location l ON c.loc_id = l.id
GROUP BY 
    l.location_name
ORDER BY 
    close_contact_number DESC, 
    l.location_name;

 15. 感染人数最多的人

-- 15) 查询感染人数最多的人员编号，姓名，和被其感染的人数。
--     感染人数由统计所得，命名为infected_number.
-- 说-明：
--     由于数据集close_contact表中的被密接者大多无诊断记录（无法知晓其是否被感染）。
--     增补数据集会影响其它评测，更有同学此前已完成评测。所以，此题暂简化为被密接者就是感染者。
-- 请用一条SQL语句实现该查询：
use covid19mon;
SELECT case_p_id,fullname,COUNT(p_id) AS infected_number
FROM close_contact JOIN person 
ON case_p_id = person.id 
GROUP BY case_p_id
HAVING infected_number>= ALL(
    SELECT COUNT(p_id) AS cnt
     FROM close_contact 
     GROUP BY case_p_id);

16. 行程记录最频繁的3个人

17. 房间数第2多的隔离点

-- 17 查询隔离点中，房间数第二多的隔离点名称和房间数。
--    请用一条SQL语句实现该查询：
use covid19mon;

SELECT 
    location_name,
    capacity
FROM (
    SELECT 
        location_name,
        capacity,
        DENSE_RANK() OVER (ORDER BY capacity DESC) AS rank_num
    FROM isolation_location
) ranked
WHERE rank_num = 2;