数据库 - 第四次实验

a)

SELECT People.pid,People.name,City.name
FROM People JOIN City ON People.cid = City.cid
WHERE People.pid = 1

结果：

b)

SELECT vaccine.name,count(*)
FROM people JOIN inoculate ON people.pid = inoculate.pid
            JOIN vaccine ON inoculate.vcid = vaccine.vcid
WHERE people.pid = 1

结果：

c)

SELECT pcrtest.result,pcrtest.pcr_time
FROM pcrtest JOIN people ON people.pid = pcrtest.pid
WHERE people.pid = 1
ORDER BY pcrtest.pcr_time DESC LIMIT 1

结果：

d)

SELECT DISTINCT city.name,city.risk_level
FROM venue JOIN city ON venue.cid = city.cid 
           JOIN visit ON visit.vid = venue.vid
WHERE visit.pid=1 AND visit.leave_time + 100 > 1000

结果：

2.

SELECT people.pid, people.name, count(pcr_latest.pcr_time)
FROM people   
LEFT JOIN (  
    SELECT pid, pcr_time   
    FROM pcrtest   
    WHERE pcr_time > 900  
) AS pcr_latest ON people.pid = pcr_latest.pid    
GROUP BY people.pid    
ORDER BY count(pcr_latest.pcr_time) ASC

结果：

SELECT people.pid,people.name,max(visit.leave_time)
FROM people JOIN visit ON people.pid = visit.pid
			JOIN venue ON venue.vid = visit.vid
WHERE visit.leave_time + 100 > 1000 AND people.cid = 2 AND venue.cid = 1
GROUP BY visit.pid,visit.vid

结果：

SELECT people.pid,people.name,vaccine.name,count(*)
FROM people JOIN inoculate ON inoculate.pid = people.pid
            JOIN vaccine ON vaccine.vcid = inoculate.vcid 
GROUP BY people.pid,vaccine.vcid
HAVING count(*) = vaccine.required_doses

结果：

CREATE VIEW positive_people(pid,pcr_time)
AS
SELECT people.pid,pcrtest.pcr_time
FROM people JOIN pcrtest ON people.pid = pcrtest.pid
WHERE pcrtest.result = '阳性'

结果：

再建一个视图，排除掉后面检测是绿码的人

CREATE VIEW positive_peopleNEW(pid,pcr_time)
AS
SELECT people.pid,max(pcrtest.pcr_time)
FROM people JOIN pcrtest ON people.pid = pcrtest.pid
GROUP BY people.pid
HAVING pcrtest.result = '阳性'

再使用以下查询：

UPDATE people  
SET status = '红码'  
WHERE pid IN (  
    SELECT pid  
    FROM positive_peopleNEW
)

结果：

SELECT people.name,venue.name,visit.enter_time,visit.leave_time,positive_people.pcr_time
FROM positive_people JOIN people ON positive_people.pid = people.pid
                     JOIN visit ON visit.pid = positive_people.pid
					 JOIN venue ON venue.vid = visit.vid
WHERE pcr_time-14<leave_time AND pcr_time>enter_time

结果：

先建立视图，找出密接：

CREATE VIEW choose
AS
SELECT DISTINCT v_ngt.pid
FROM visit v_ngt JOIN visit v_pst ON v_ngt.vid = v_pst.vid
                 JOIN positive_people ON positive_people.pid =v_pst.pid
WHERE  v_ngt.pid <> v_pst.pid AND 
       v_pst.enter_time < v_ngt.leave_time AND
	   v_ngt.enter_time < v_pst.leave_time AND
	   MIN(v_pst.leave_time,v_ngt.leave_time) > pcr_time - 14 AND
	   MAX(v_pst.enter_time,v_ngt.enter_time) < pcr_time

再进行修改

UPDATE people  
SET status = '黄码'  
WHERE status = '绿码' AND pid IN choose

结果：