MySQL数据库头歌训练17关

最新推荐文章于 2023-10-27 14:43:30 发布

冬笔弗瑞

最新推荐文章于 2023-10-27 14:43:30 发布

阅读量1.3k

点赞数 4

文章标签：数据库 mysql

本文链接：https://blog.csdn.net/qq_45311021/article/details/131095682

版权

文章提供了一系列SQL查询示例，涉及人流量统计、隔离点使用情况、行程接续、特定人员行程、地点名称筛选、接触者追踪、隔离点现状、空房间数、接触者名单、密接者数量、感染人数、行程频繁度和隔离点容量等多个方面，用于疫情监控和管理。

摘要由CSDN通过智能技术生成

注：整理于互联网，仅供参考

第1关人流量大于30的地点

use covid19mon;

-- 1) 查询累计人流量大于30的地点名称和累计人流量，累积人流量请用visitors作标题名称。
--    查询结果按照人流量从高到低排序，人流量相同时，依地点名称顺序排序。
--    （注意：同一人多次逛同一地点，去几次算几次）
--    请用一条SQL语句实现该查询：
 select location_name,count(*) as 'visitors'
    from location,itinerary,person
    where p_id=person.id and loc_id=location.id#内连接
    group by location_name having visitors>30###所属条件
    order by visitors desc,location_name;




/*  end  of  your code  */

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

 use covid19mon;

-- 2) 查询每个隔离地及该地正在进行隔离的人数，以number为隔离人数的标题.
--    查询结果依隔离人数由多到少排序。人数相同时，依隔离地点名排序。
--    请用一条SQL语句实现该查询：
select isolation_location.location_name,count(*) as 'number'
from isolation_record,isolation_location,person
where isolation_location.id=isol_loc_id and person.id=p_id and isolation_record.state in (1)
group by location_name
order by number desc;


/*  end  of  your code  */

第3关接续行程

 use covid19mon;

-- 2) 查询行程表中所有属于同一个人的接续行程信息。输出内容包括：
-- 人员编号,姓名,重合时间,起始地点id,起始地点,结束地点id,结束地点。
-- 查询结果依人员编号排序。
-- 请用一条SQL语句实现该查询：
select     person.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 AS i1
JOIN itinerary AS i2 ON i1.e_time = i2.s_time and i1.p_id = i2.p_id
JOIN location AS l1 ON l1.id = i1.loc_id
JOIN location AS l2 ON l2.id = i2.loc_id
join person on person.id = i1.p_id
where person.id > 30
order by person.id,reclosing_time;


/*  end  of  your code  */

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

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

--    请用一条SQL语句实现该查询：
use covid19mon;

use covid19mon;
select fullname,telephone,location_name,s_time,e_time
from person left outer join itinerary on person.id=itinerary.p_id
left outer join location on location.id=itinerary.loc_id 
where fullname in ('充珉瑶','贾涵山')
order by person.id desc,s_time;



/*  end  of  your code  */

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

-- 5) 查询地名中带有‘店’字的地点编号和名称。查询结果按地点编号排序。
--    请用一条SQL语句实现该查询：
use covid19mon;
select * from location where location_name like '%店%' order by id;


/*  end  of  your code  */

第6关确诊者的接触者

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

/*select fullname,telephone,s_time,e_time
from person,itinerary,location
where person.id=itinerary.p_id and location.id=itinerary.loc_id
and fullname='富昱瑛'
order by fullname desc;*/
select fullname,telephone
from person,itinerary,location
where person.id=itinerary.p_id and location.id=itinerary.loc_id
and (e_time>='2021-2-2 20:05:40' and s_time<='2021-2-2 21:25:40')
and location_name='活动中心'
order by fullname;




/*  end  of  your code  */

第7关仍在使用的隔离点

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




/*  end  of  your code  */

第8关查询有出行记录的人员

-- 8) 用一条带exists关键字的SQL语句查询前30位有出行记录的人员姓名和电话。查询结果按照人员编号排序。
--    请用一条SQL语句实现该查询：
use covid19mon;


select fullname,telephone
from person
where exists(select *
from itinerary
where person.id=itinerary.p_id
order by person.id
)
limit 30;

/*  end  of  your code  */

第9关去过“Today便利店“的人数

-- 9) 写一条带 EXISTS 子查询的SQL语句实现下述查询要求：
--   查询人员表中去过地点“Today便利店”的人数。请给统计出的人数命名为number。
--   请用一条SQL语句实现该查询：
use covid19mon;



SELECT COUNT(DISTINCT p_id) AS number
FROM itinerary
WHERE loc_id IN (
    SELECT id
    FROM location
    WHERE location_name = 'Today便利店'
);


/*  end  of  your code  */

第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;









/*  end  of  your code  */

第11关隔离点的现状视图

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

use covid19mon;

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 END) AS occupied
FROM
    isolation_location
LEFT JOIN
    isolation_record ON isolation_location.id = isolation_record.isol_loc_id
GROUP BY
    isolation_location.id, isolation_location.location_name, isolation_location.capacity;


/*  end  of  your code  */

第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;



/*  end  of  your code  */

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

-- 13) 筛查发现，靳宛儿为无症状感染者。现需查询其接触者姓名名单和电话，以便通知并安排隔离。查询结题按姓名排序。
--    凡行程表中，在同一地点逗留时间与靳宛儿有交集的，均视为接触者。
--    请用一条SQL语句实现该查询：
use covid19mon;

SELECT
    p.fullname,
    p.telephone
FROM
    person AS p
INNER JOIN
    itinerary AS i ON p.id = i.p_id
INNER JOIN
    itinerary AS j ON i.loc_id = j.loc_id
WHERE
    i.p_id <> j.p_id
    AND i.s_time < j.e_time
    AND i.e_time > j.s_time
    AND j.p_id = (SELECT id FROM person WHERE fullname = '靳宛儿')
ORDER BY
    p.fullname;






/*  end  of  your code  */

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

-- 14) 依据密切接触表的内容查询每个地点的密切接触者的数量，列出内容包括：地点名称，密接者人数。
--     人数由统计获得，列名命名为close_contact_number.查询结果依密接者人数降序排列。
--     密接者人数相同时，依地点名称排序。
--    请用一条SQL语句实现该查询：
use covid19mon;

select location_name,count(*) as close_contact_number
from location,close_contact
where close_contact.loc_id=location.id
group by location_name
order by close_contact_number desc,location_name;




/*  end  of  your code  */

第15关感染人数最多的人

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

select case_p_id,fullname,count(*) as infected_number
from close_contact,person
where case_p_id=person.id
group by case_p_id,fullname
ORDER BY infected_number desc
limit 1;





/*  end  of  your code  */

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

-- 16) 查询2021-02-02 10:00:00到14:00:00期间，行程记录最频繁的3个人的姓名及行程记录条数。
--     记录条数命名为record_number. 记录数并列的，按姓名顺序排列。
--    请用一条SQL语句实现该查询：
use covid19mon;

select fullname,count(*) as record_number
from person,itinerary
where person.id=p_id
and ((s_time>='2021-02-02 10:00:00' and s_time<='2021-02-02 14:00:00') or(e_time>='2021-02-02 10:00:00' and e_time<='2021-02-02 14:00:00'))
group by fullname
order by record_number desc,fullname
limit 3;





/*  end  of  your code  */

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

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

select location_name,capacity
from isolation_location
where capacity<(select capacity from isolation_location
order by capacity desc limit 1)
limit 1;





/*  end  of  your code  */