目录显示

GaussDB(for openGauss)数据库实验

实验一GaussDB(for openGauss)数据库创建与维护实验

实验步骤

熟悉GaussDB(for openGauss)数据库的创建过程；
创建一个名为“疫情数据”的数据库；
删除“疫情数据”数据库。

创建一个名为“疫情数据”的数据库

CREATE TABLE "疫情数据" (
    "病例号" INT,
    "省" VARCHAR(30),
    "市" VARCHAR(30),
    "区" VARCHAR(30),
    "日期" DATE,
    "性别" CHAR(3),
    "年龄" INT,
    "患者信息" VARCHAR(1024),
    "其它信息" VARCHAR(1024),
    "信息来源" VARCHAR(30)
);

删除“疫情数据”数据库

DROP TABLE "疫情数据" ;

实验二数据库表的创建与维护实验

实验步骤

熟悉课程实验背景知识；
使用GAUSSDB(FOR OPENGAUSS)数据库软件创建相应的表；
将提供的数据导入各表，掌握GAUSSDB(FOR OPENGAUSS)数据库数据导入的方法；
注意：
(1) 表中空列的处理；
(2) 表结构与数据类型的匹配。
修改 “病例基本信息”表数据，增加名为“备注”的列,数据类型为vachar()型；
修改 “病例基本信息”表数据，将 “备注”列的数据类型改为int；
修改 “病例基本信息”表数据，删除“备注”列；
删除“病例基本信息”数据表。

将提供的数据导入各表

修改 “病例基本信息”表数据，增加名为“备注”的列,数据类型为vachar()型

alter table "病例基本信息" add "备注" varchar(1024);

修改 “病例基本信息”表数据，将 “备注”列的数据类型改为int

alter table "病例基本信息" MODIFY "备注"  int;

修改 “病例基本信息”表数据，删除“备注”列

alter table "病例基本信息" drop "备注";

删除“病例基本信息”数据表

drop table "病例基本信息"

实验三数据查询实验

一、实验目的

通过对实验二建立的数据库关系表的各种查询的操作，加深对SQL语言和PostgreSQL查询语言的了解，掌握相关查询语句的语法及使用方法。

二、实验内容

单表查询

1-1 查询国内确诊病例基本信息的所有信息来源。

1-2 给出河南省、西藏自治区、台湾省的英文名称和人口数。

1-3 查询2021年1月20日各省现有确诊病例数据，按现有确诊病例数降序排列输出。

1-4 顺义区中风险地区的数量。

1-5 计算截至2021年1月20日全国累计确诊病例数。

1-6 查询1005号病例确诊后，其所在市新增的所有确诊病例。

1-7 在“病例基本信息表”中查询石家庄市在2021年1月11日当天以及之前的所有60岁以上的患者信息。

1-8 统计截止到2020年12月30日美国累计确诊病例数最多的10个州。

1-9 统计截至2021年1月20号中国发病率最高的人群（人群按照年龄划分，儿童<18，18<=青壮年<60，老年>=60）。
多表查询：

2-1 借助病例行程信息粗略查询曾去过“源升品质生活坊”的所有患者的基本信息。

2-2 根据病例行程信息表和病例基本信息表，查询行程信息中存在“家庭聚餐”的病例被确诊的日期。

2-3 对比中美两国累计确诊病例数，输出格式为(日期，中国累计确诊，美国累计确诊)。

2-4 计算截止到2021年1月20日，美国有些县的累计确诊是同一个州的其他县的2倍或以上，列出这些县，以及他们所在的州和他们的累计确诊。

2-5 计算世界上人口数排名前10位的国家地区。

2-6 列出美国人口超千万的大州中，截至2021年1月20日新冠肺炎疫情死亡率超过2%的州。

2-7 截至2021年1月20日，河北省哪些区出现了新冠确诊病例但不属于中高风险地区。

2-8 在病例行程信息表的基础上根据病例基本信息表，查询河北省病例的全部信息。
嵌套查询：

3-1 查询披露的确诊患者信息中年龄最大的患者，输出其基本信息。(未注明年龄的患者不进行比较)。

3-2 查询2020年12月份新增确诊患者最多的城市。

3-3 结合“全国各省参考信息表”和“病例基本信息表”给出没有新增确诊病例或未披露病例信息的省份。

3-4 2021年1月20日全国中高风险地区所在省中，哪些省在1月20日没有新增确诊信息披露。

3-5 根据病例基本信息表查询一月份国内新增患者病例最多的城市。

3-6 查询除中美两国以外的其余国家中，进入2021年以来单日新增确诊病例始终不低于一万例的国家。

1-1 查询国内确诊病例基本信息的所有信息来源。

方法一：

SELECT "信息来源" FROM "病例基本信息" GROUP BY "信息来源";

结果：

新京报
澎湃新闻
青冈县疾病预防控制中心
大众网
今晚报
中国新闻网
广西新闻网
辽宁日报
黑龙江卫健委网站
河北日报
黑龙江省齐齐哈尔市疾控中心
黑龙江省黑河市政府新闻办
北京青年报
上海本地宝
松原发布
光明网
望奎县新冠肺炎疫情防控工作指挥部
北京市疾病预防控制中心
哈尔滨市人民政府网站
安达市疾控中心
环球网
健康北京
浙江省卫健委
哈尔滨市疾病预防控制中心
黑龙江卫健委
央视新闻客户端
北京日报
大连发布
健康成都
央视新闻
津云
界面新闻
河北新闻网
齐齐哈尔市卫生健康委员会
北京都市
中国快讯
黑龙江省大庆市疾病预防控制中心
黑河市应对新型冠状病毒感染肺炎疫情工作领导小组指挥部
沈阳卫健委
人民日报
大连卫健委
吉林省卫生健康委
黑龙江省卫健委
四川日报

方法二：

SELECT DISTINCT "信息来源" FROM "病例基本信息";

结果同上：

1-2 给出河南省、西藏自治区、台湾省的英文名称和人口数。

创建表"全国各省参考信息"：

CREATE TABLE "全国各省参考信息" (
    "中文名称" VARCHAR(1024),
    "英文名称" VARCHAR(1024),
    "组合码" VARCHAR(1024),
    "人口数" BIGINT,
    "省会城市" VARCHAR(1024),
    "纬度" DECIMAL,
    "经度" DECIMAL
);

查询：

SELECT "中文名称", "英文名称", "人口数"
FROM "全国各省参考信息"
WHERE "中文名称" = '河南省'
    OR "中文名称" = '西藏自治区'
    OR "中文名称" = '台湾省';

结果：

1-3 查询2021年1月20日各省现有确诊病例数据，按现有确诊病例数降序排列输出。

查询：现有确诊 = 累计确诊 - 累计治愈

select 省, 累计确诊-累计治愈 as 现有确诊
from 全国各省累计数据统计
where 日期='2021-01-20'
order by 现有确诊 desc

结果：

1-4 顺义区中风险地区的数量。

创建表"全国城市风险等级"：

CREATE TABLE "全国城市风险等级" (
    省 VARCHAR(1024),
    市 VARCHAR(1024),
    区 VARCHAR(1024),
    地址详情 VARCHAR(1024),
    风险等级 VARCHAR(1024)
);

查询：

SELECT COUNT(*)
FROM "全国城市风险等级"
WHERE "区" = '顺义区'
    AND "风险等级" = '中风险地区';

结果：4

1-5 计算截至2021年1月20日全国累计确诊病例数。

创建表"全国各省累计数据统计"：

CREATE TABLE "全国各省累计数据统计" (
    日期 DATE,
    省 VARCHAR(1024),
    累计确诊 INT,
    累计治愈 INT,
    累计死亡 INT
);

查询：

SELECT SUM("累计确诊")
FROM "全国各省累计数据统计"
WHERE "日期" = '2021/1/20';

结果：98545

1-6 查询1005号病例确诊后，其所在市新增的所有确诊病例。

方法一：

注：由于不能确定与1005号病例同天确诊的病例是在1005号病例前或后确诊，所以仅查询在1005号确诊日期之后的病例。

SELECT "病例号"
FROM "病例基本信息"
WHERE 市 = (
        SELECT "市"
        FROM "病例基本信息"
        WHERE "病例号" = 1005
    )
    AND "日期" > (
        select 日期 
        from 病例基本信息 
        where 病例号 = 1005
    );

结果：982

方法二：

select 病例号 
from 病例基本信息, (select 市, 日期 from 病例基本信息 where 病例号 = 1005) as 目标病人
where 病例基本信息.市 = 目标病人.市 and 病例基本信息.日期>目标病人.日期

结果：982

1-7 在“病例基本信息表”中查询石家庄市在2021年1月11日当天以及之前的所有60岁以上的患者信息。

查询：

SELECT "患者信息"
FROM "病例基本信息"
WHERE "日期" <= '2021/1/11'
    AND "年龄" > 60
    AND "市" = '石家庄市';

结果：

	患者信息
1	`女，65岁，井陉县秋树坡村人。`
2	`确诊病例6：女，78岁，藁城区增村镇南桥寨村人。`
3	`确诊病例8：女，75岁，藁城区增村镇小果庄村人。`
4	`确诊病例9：女，68岁，藁城区增村镇小果庄村人。`
5	`确诊病例11：女，77岁，藁城区增村镇南桥寨村人。`
6	`确诊病例12：男，70岁，藁城区增村镇南桥寨村人。`
7	`确诊病例16：女，67岁，藁城区增村镇南桥寨村人。`
8	`确诊病例20：女，68岁，藁城区增村镇小果庄村人，现住长安区丰收路红珊湾小区。`
9	`确诊病例22：女，68岁，藁城区增村镇南桥寨村人。`
10	`确诊病例23：女，70岁，藁城区增村镇北桥寨村人。`
11	`确诊病例24：女，73岁，藁城区常安镇北楼村人。`
12	`确诊病例26：女，64岁，藁城区增村镇小果庄村人。`
13	`确诊病例28：男，66岁，藁城区增村镇小果庄村人。在村中开办诊所。`
14	`确诊病例39：女，71岁，新乐市马头铺镇张家庄村人。`
15	`确诊病例40：男，67岁，藁城区增村镇刘家佐村人。`
16	`确诊病例46：男，66岁，藁城区增村镇小果庄村人。`
17	`确诊病例47：女，68岁，藁城区增村镇南桥寨村人。`
18	`确诊病例48：男，64岁，藁城区增村镇南桥寨村人。`
19	`确诊病例52：男，67岁，藁城区增村镇杨马村人。`
20	`确诊病例53：女，78岁，藁城区增村镇南桥寨村人。`
21	`确诊病例58：女，63岁，藁城区增村镇小果庄村人。`
22	`确诊病例61：男，67岁，藁城区增村镇东桥寨村人。`
23	`确诊病例64：女，72岁，藁城区增村镇南桥寨村人。`
24	`确诊病例66：女，85岁，藁城区增村镇南桥寨村人。`
25	`确诊病例67：女，68岁，藁城区增村镇南桥寨村人。`
26	`确诊病例68：男，70岁，新乐市协神乡牛家庄村人。`
27	`确诊病例69：女，67岁，藁城区增村镇小果庄村人。`
28	`确诊病例71：男，72岁，藁城区岗上镇杜村人。`
29	`确诊病例74：女，62岁，新乐市长寿街道东长寿村人，村内经营棋牌室。`
30	`确诊病例1：女，69岁，藁城区增村镇小果庄村人。`
31	`确诊病例4：女，65岁，藁城区增村镇南桥寨村人。`
32	`确诊病例10：女，64岁，藁城区张家庄镇鲍家庄村人。`
33	`确诊病例11：女，62岁，藁城区增村镇小果庄村人。`
34	`确诊病例16：女，82岁，藁城区增村镇小果庄村人。`
35	`确诊病例19：男，61岁，藁城区增村镇东桥寨村人。`
36	`确诊病例29：女，71岁，藁城区增村镇小果庄村人。`
37	`确诊病例30：男，62岁，新乐市长寿街道东长寿村人。`
38	`确诊病例33：男，68岁，藁城区增村镇小果庄村人。`
39	`确诊病例35：女，71岁，藁城区增村镇杨马村人。`
40	`确诊病例39：男，79岁，新乐市马头铺镇张家庄村人，是第38号确诊病例的父亲。`
41	`确诊病例4：女，70岁，藁城区增村镇小果庄村人。`
42	`确诊病例5：男，68岁，现住鹿泉区上庄镇观峰嘉邸小区。`
43	`确诊病例11：女，65岁，藁城区增村镇牛家庄村人。`
44	`女，66岁，藁城区增村镇小果庄村人。`
45	`女，66岁，藁城区增村镇小果庄村人。`
46	`女，86岁，藁城区增村镇小果庄村人。`
47	`男，75岁，新乐市邯邰镇坚固村人。`
48	`女，72岁，藁城区增村镇刘家佐村人。`
49	`女，62岁，藁城区增村镇小果庄村人。`
50	`女，65岁，藁城区增村镇刘家佐村人。`

1-8 统计截止到2020年12月30日美国累计确诊病例数最多的10个州。

创建表"美国各州县确诊与死亡数统计"：

CREATE TABLE "美国各州县确诊与死亡数统计" (
    日期 DATE,
    州 VARCHAR(1024),
    县 VARCHAR(1024),
    累计确诊 INT,
    累计死亡 INT
);

查询：

SELECT "州"
FROM "美国各州县确诊与死亡数统计"
WHERE "日期" = '2020/12/30'
group by 州
ORDER BY sum(累计确诊) DESC 
LIMIT 10;

结果：

	州
1	`California`
2	`Texas`
3	`Florida`
4	`New York`
5	`Illinois`
6	`Ohio`
7	`Georgia`
8	`Pennsylvania`
9	`Tennessee`
10	`North Carolina`

1-9 统计截至2021年1月20号中国发病率最高的人群（人群按照年龄划分，儿童<18，18<=青壮年<60，老年>=60）。

方法一：

select 人群 from 
(SELECT  CASE 
WHEN 年龄<18 THEN '儿童'
WHEN 年龄>=18 and 年龄 <60 THEN '青壮年'
WHEN 年龄>60 THEN '老年' 
ELSE '年龄未知'
END AS 人群
FROM 病例基本信息
WHERE 日期 <= '2021-1-20')
group by 人群
order by count(*) desc
limit 1

结果：青壮年

方法二：使用三次子查询，分别在where中实现年龄段的划分。每次子查询得到包含“数量”与“年龄段”两列的一条记录，用union将三次次查询的结果集合并。最后用“数量”字段倒序排序，得到发病率最高的“年龄段”

注：由于不知道每个年龄段的总人数，因此本题中的发病率仅按发病人数统计

SELECT 总表.年龄段 FROM 
((SELECT COUNT(*) as 数量, '儿童' as 年龄段
FROM "病例基本信息"
WHERE "日期" <= '2021/1/20'
AND "年龄" < 18) UNION
(SELECT COUNT(*) as 数量, '青壮年' as 年龄段
FROM "病例基本信息"
WHERE "日期" <= '2021/1/20'
AND "年龄" >= 18 AND "年龄" < 60) UNION
(SELECT COUNT(*) as 数量, '老年' as 年龄段
FROM "病例基本信息"
WHERE "日期" <= '2021/1/20'
AND "年龄" >= 60))as 总表
order by 数量 desc
limit 1

结果：青壮年

2-1 借助病例行程信息粗略查询曾去过“源升品质生活坊”的所有患者的基本信息。

创建表"病例行程信息"：

CREATE TABLE 病例行程信息 (
    行程号 INT,
    病例号 INT,
    日期信息 DATE,
    行程信息 varchar(1024)
);

查询：使用like模糊查询。使用where-in查询属于子查询结果集的病例。

注：假设题目中的患者基本信息的含义是病例基本信息的”患者信息“字段

SELECT 患者信息 FROM 病例基本信息 WHERE 病例号 IN (SELECT 病例号 FROM 病例行程信息 WHERE 行程信息 LIKE '%源升品质生活坊%');

结果：

	患者信息
1	`确诊病例2：女，1948年出生`
2	`无症状感染者9：女，1945年出生`
3	`无症状感染者13：男，1943年出生`
4	`无症状感染者3：男，1976年出生`

2-2 根据病例行程信息表和病例基本信息表，查询行程信息中存在“家庭聚餐”的病例被确诊的日期。

查询：

select 日期 from 病例基本信息 where 病例号 in (select 病例号 from 病例行程信息 where 行程信息 like '%家庭聚餐%')

结果：

	日期
1	`2021-01-13`
2	`2021-01-13`
3	`2021-01-05`
4	`2021-01-04`

2-3 对比中美两国累计确诊病例数，输出格式为(日期，中国累计确诊，美国累计确诊)。

创建表"各国疫情数据统计"：

create table 各国疫情数据统计 (
    日期  date,
    国家  varchar(1024),
    省州  varchar(1024),
    累计确诊    int,
    累计治愈    int,
    累计死亡    int
)

查询：使用natual join，按照日期合并两个子查询的结果

-- 思路
create view 美国 as select 日期, sum(累计确诊) from 各国疫情数据统计 where 国家='US' group by 日期

create view 中国 as select 日期, sum(累计确诊) from 全国各省累计数据统计 group by 日期


-- 最终的sql
select 日期, 中国累计确诊, 美国累计确诊 
from (select 日期, sum(累计确诊) as 美国累计确诊 from 各国疫情数据统计 where 国家='US' group by 日期) as 美国
natural join (select 日期, sum(累计确诊) as 中国累计确诊 from 全国各省累计数据统计 group by 日期) as 中国 
order by 日期

结果：

	日期	中国累计确诊	美国累计确诊
1	`2020-11-22`	`92117`	`12347905`
2	`2020-11-23`	`92212`	`12521898`
3	`2020-11-24`	`92297`	`12697001`
4	`2020-11-25`	`92403`	`12879677`
5	`2020-11-26`	`92489`	`12991818`
6	`2020-11-27`	`92587`	`13199752`
7	`2020-11-28`	`92682`	`13355018`
8	`2020-11-29`	`92815`	`13495104`
9	`2020-11-30`	`92903`	`13655220`
10	`2020-12-01`	`92994`	`13842652`
11	`2020-12-02`	`93114`	`14044625`
12	`2020-12-03`	`93222`	`14267421`
13	`2020-12-04`	`93349`	`14499637`
14	`2020-12-05`	`93468`	`14714853`
15	`2020-12-06`	`93578`	`14895691`
16	`2020-12-07`	`93671`	`15089952`
17	`2020-12-08`	`93783`	`15313819`
18	`2020-12-09`	`93899`	`15535463`
19	`2020-12-10`	`94026`	`15766130`
20	`2020-12-11`	`94125`	`16004659`
21	`2020-12-12`	`94218`	`16221125`
22	`2020-12-13`	`94329`	`16408428`
23	`2020-12-14`	`94428`	`16601499`
24	`2020-12-15`	`94538`	`16810792`
25	`2020-12-16`	`94627`	`17056440`
26	`2020-12-17`	`94735`	`17295460`
27	`2020-12-18`	`94822`	`17546621`
28	`2020-12-19`	`94954`	`17738237`
29	`2020-12-20`	`95051`	`17925587`
30	`2020-12-21`	`95151`	`18123968`
31	`2020-12-22`	`95229`	`18321157`
32	`2020-12-23`	`95299`	`18550002`
33	`2020-12-24`	`95384`	`18743916`
34	`2020-12-25`	`95461`	`18841414`
35	`2020-12-26`	`95542`	`19067574`
36	`2020-12-27`	`95633`	`19222891`
37	`2020-12-28`	`95721`	`19396787`
38	`2020-12-29`	`95798`	`19595354`
39	`2020-12-30`	`95877`	`19827770`
40	`2020-12-31`	`95964`	`20061903`
41	`2021-01-01`	`96024`	`20215297`
42	`2021-01-02`	`96087`	`20515083`
43	`2021-01-03`	`96161`	`20723157`
44	`2021-01-04`	`96247`	`20906563`
45	`2021-01-05`	`96311`	`21140217`
46	`2021-01-06`	`96399`	`21394210`
47	`2021-01-07`	`96485`	`21671278`
48	`2021-01-08`	`96563`	`21963383`
49	`2021-01-09`	`96691`	`22225720`
50	`2021-01-10`	`96825`	`22438881`
51	`2021-01-11`	`96921`	`22653070`
52	`2021-01-12`	`97096`	`22879069`
53	`2021-01-13`	`97276`	`23108826`
54	`2021-01-14`	`97449`	`23343727`
55	`2021-01-15`	`97617`	`23584671`
56	`2021-01-16`	`97776`	`23785679`
57	`2021-01-17`	`97940`	`23962788`
58	`2021-01-18`	`98165`	`24105873`
59	`2021-01-19`	`98324`	`24281531`
60	`2021-01-20`	`98545`	`24464045`

2-4 计算截止到2021年1月20日，美国有些县的累计确诊是同一个州的其他县的2倍或以上，列出这些县，以及他们所在的州和他们的累计确诊。

查询：子查询查出每个州最低的累计确诊数，用natual join将子查询的结果表与“美国各州县确诊与死亡数统计”表链接，则可以得到“美国各州县确诊与死亡数统计”表中每个县所在州的最低确诊人数。筛选累计确诊 >= 最低累计确诊*2即可。

注：只要一个州中有一个县的累计确诊为0，这个州中所有的县都会进入结果集。观察到表中存在如下的记录：

日期	州	县	累计确诊	累计死亡
2020/11/22	New Mexico	Out of NM	0	0
2020/11/22	Oregon	Unassigned	0	0
……	……	……	……	……

所以应当在子查询中排除掉累计确诊为0的记录。

select 县, 州, 累计确诊 
from 美国各州县确诊与死亡数统计 
natural join (select 州, min(累计确诊) as 最低累计确诊 from 美国各州县确诊与死亡数统计 where 累计确诊 != 0 group by 州) 
where 累计确诊 >= 最低累计确诊*2

结果：

2-5 计算世界上人口数排名前10位的国家地区。

创建表"参考信息"：

create table 参考信息 (
    组合码 varchar(1024),
    国家  varchar(1024),
    省州  varchar(1024),
    市县  varchar(1024),
    纬度  decimal,
    经度  decimal,
    人口数 int8
);

alter table 参考信息 modify 人口数 bigint;

查询：从“参考信息”表中查询除中国以外的国家的人口数，从“全国各省参考信息”表中查询中国人口数，每个子查询得到“国家”、“人口数”两列信息，用union合并两个子查询的结果集。

注：

由于“参考信息”表中有两个虚构的国家，用人口数 is not null将这两个国家排除掉。
经过观察，“参考信息”表中，若一个省州有多个市县，则会有一行统计省州的总人口；若一个国家有多个省州，则会有一行统计国家的总人口。因此不能按照group by 国家来处理，应该按照组合码 = 国家来处理。

查询：

select 国家, 人口数 
from (select 国家, 人口数 as 人口数 from 参考信息 where 人口数 is not null and 组合码 = 国家)
union (select 'China' as 国家, sum(人口数) as 人口数 from 全国各省参考信息) 
order by 人口数 desc 
limit 10

结果：

	国家	人口数
1	`China`	`1428493105`
2	`India`	`1380004385`
3	`US`	`329466283`
4	`Indonesia`	`273523621`
5	`Pakistan`	`220892331`
6	`Brazil`	`212559409`
7	`Nigeria`	`206139587`
8	`Bangladesh`	`164689383`
9	`Russia`	`145934460`
10	`Mexico`	`127792286`

2-6 列出美国人口超千万的大州中，截至2021年1月20日新冠肺炎疫情死亡率超过2%的州。

思路：

create view 美国大州 as
select 省州 as 州, sum(人口数) as 人口数 from 参考信息 where 国家='US' group by 省州 having sum(人口数)>=10000000

create view 州累计死亡 as
select 州, sum(累计死亡) as 累计死亡 from 美国各州县确诊与死亡数统计 where 日期 <='2021/1/20' group by 州

select 州 from 州累计死亡 natural join 美国大州 where 累计死亡>(人口数*0.02)

最终的sql语句：

select 州
from (select 州, sum(累计死亡) as 累计死亡 from 美国各州县确诊与死亡数统计 where 日期 <='2021/1/20' group by 州) as 州累计死亡
natural join (select 省州 as 州, sum(人口数) as 人口数 from 参考信息 where 国家='US' group by 省州 having sum(人口数)>=10000000) as 美国大州
where 累计死亡>(人口数*0.02)

结果：

	州
1	`Massachusetts`
2	`Wisconsin`
3	`Ohio`
4	`South Carolina`
5	`Georgia`
6	`New Jersey`
7	`Florida`
8	`Maryland`
9	`Pennsylvania`
10	`Tennessee`
11	`New York`
12	`Indiana`
13	`Minnesota`
14	`Colorado`
15	`Arizona`
16	`Texas`
17	`Michigan`
18	`Missouri`
19	`Illinois`

2-7 截至2021年1月20日，河北省哪些区出现了新冠确诊病例但不属于中高风险地区。

解决思路：

select distinct 区 from 病例基本信息 where 省='河北省' and 日期<='2021/1/20' and 区 is not null -- 这一天前所有出现病例的河北省的区

select distinct 区 from 全国城市风险等级 where 省='河北省' -- 中高风险区

最终的sql语句：

select A.区 
from (select distinct 区 from 病例基本信息 where 省='河北省' and 日期<='2021/1/20' and 区 != 'null') as A
left join(select distinct 区 from 全国城市风险等级 where 省='河北省') as B 
on A.区 = B.区
where B.区 is null

结果：

	区
1	`栾城`
2	`井陉县`
3	`裕华市`
4	`正定`

2-8 在病例行程信息表的基础上根据病例基本信息表，查询河北省病例的全部信息。

查询：由于题目要求在病例行程信息表的基础上，所以使用natural left outer join

select * from 病例行程信息 natural left outer join 病例基本信息 where 省 = '河北省'

结果：

由于信息过多，仅列出几条供参考

	病例号	行程号	日期信息	行程信息	省	市	区	日期	性别	年龄	患者信息	其它信息	信息来源
282	`116`	`755`	`2020年12月26日至27日`	`居家无外出`	`河北省`	`石家庄市`	`藁城区`	`2021-01-18`	`男`	`14`	`确诊病例32：男，14岁，藁城区增村镇增村人。`	`暂无`	`河北日报`
283	`116`	`756`	`12月28日`	`下午乘坐511路公交车（从增村站到刘家佐北站）到藁城区第七中学`	`河北省`	`石家庄市`	`藁城区`	`2021-01-18`	`男`	`14`	`确诊病例32：男，14岁，藁城区增村镇增村人。`	`暂无`	`河北日报`
284	`116`	`757`	`1月15日`	`诊断为无症状感染者`	`河北省`	`石家庄市`	`藁城区`	`2021-01-18`	`男`	`14`	`确诊病例32：男，14岁，藁城区增村镇增村人。`	`暂无`	`河北日报`

3-1 查询披露的确诊患者信息中年龄最大的患者，输出其基本信息。(未注明年龄的患者不进行比较)。

由于我不太理解题目中输出其基本信息是指输出病例基本信息中的全部信息，还是”患者信息”一列，所以都做了查询。

查询一：假设题目中所述基本信息代表“病例基本信息”表中的全部属性

select * from 病例基本信息 where 年龄 = (select max(年龄) from 病例基本信息 where 年龄 is not null)

结果：

1103
辽宁省
沈阳市
皇姑区
2020-12-29
女
95
张某某，女，95岁
退休人员，家庭住址为沈阳市皇姑区昆山西路塔湾新城小区。
沈阳卫健委

查询二：假设题目中所述基本信息代表“病例基本信息”表中的“患者信息”

select 患者信息 from 病例基本信息 where 年龄 = (select max(年龄) from 病例基本信息)

结果：张某某，女，95岁

3-2 查询2020年12月份新增确诊患者最多的城市。

思路：

select 省, 累计确诊 from 全国各省累计数据统计 where 日期='2020-12-31' as A

select 省, 累计确诊 from 全国各省累计数据统计 where 日期='2020-11-30' as B

select A.省, A.累计确诊-B.累计确诊 from A join B on A.省 = B.省 order by A.累计确诊-B.累计确诊 desc limit 1

最终的sql语句：

select A.省, A.累计确诊-B.累计确诊 as 新增确诊 
from (select 省, 累计确诊 from 全国各省累计数据统计 where 日期='2020-12-31') as A 
join (select 省, 累计确诊 from 全国各省累计数据统计 where 日期='2020-11-30') as B 
on A.省 = B.省
order by A.累计确诊-B.累计确诊 desc
limit 1

结果：香港特别行政区

3-3 结合“全国各省参考信息表”和“病例基本信息表”给出没有新增确诊病例或未披露病例信息的省份。

-- 思路
select 省 from 病例基本信息 group by 省 having 省 is not null as A

select 中文名称 from 全国各省参考信息 
left join A
on 全国各省参考信息.中文名称 = A.省
where A.省 is NULL

-- 最终的sql语句
select 中文名称 from 全国各省参考信息 
left join (select 省 from 病例基本信息 group by 省 having 省 is not null) as A
on 全国各省参考信息.中文名称 = A.省
where A.省 is NULL

结果：

	中文名称
1	`安徽省`
2	`重庆市`
3	`福建省`
4	`甘肃省`
5	`广东省`
6	`广西省`
7	`贵州省`
8	`海南省`
9	`河南省`
10	`湖北省`
11	`湖南省`
12	`江苏省`
13	`江西省`
14	`宁夏省`
15	`青海省`
16	`陕西省`
17	`台湾省`
18	`西藏自治区`
19	`新疆维吾尔族自治区`
20	`云南省`
21	`浙江省`
22	`香港特别行政区`
23	`澳门特别行政区`

3-4 2021年1月20日全国中高风险地区所在省中，哪些省在1月20日没有新增确诊信息披露。

查询：最后使用distinct来去重

select distinct 全国城市风险等级.省 
from 全国城市风险等级 
left join (select 省 from 病例基本信息 where 日期='2021-01-20' group by 省) as A 
on 全国城市风险等级.省 = A.省
where A.省 is null

结果：

	省
1	`吉林省`
2	`辽宁省`

3-5 根据病例基本信息表查询一月份国内新增患者病例最多的城市。

方法一：新增 = 最高新增

```sql
-- 思路
select 市, count(*) as 新增 from 病例基本信息 where 日期>='2021-1-1' and 日期<'2021-2-1' group by 市 as A

select max(新增) as 最高新增 from (select 市, count(*) as 新增 from 病例基本信息 where 日期>='2021-1-1' and 日期<'2021-2-1' group by 市) as B

select 市 from A where 新增 = 最高新增

-- 最终的sql语句
select 市
from (select 市, count(<em>) as 新增 from 病例基本信息 where 日期>='2021-1-1' and 日期<'2021-2-1' group by 市)
where 新增 = (select max(新增) as 最高新增 from (select 市, count(</em>) as 新增 from 病例基本信息 where 日期>='2021-1-1' and 日期<'2021-2-1' group by 市))

-- 使用with简化
with subtable as (select 市, count(*) as 新增 from 病例基本信息 where 日期>='2021-1-1' and 日期<'2021-2-1' group by 市)
select 市
from subtable
where 新增 = (select max(新增) as 最高新增 from subtable)

```

结果：石家庄市（截图分别为不使用with与使用with）

方法二：新增 >= all

select 市 
from (select 市, count(*) as 新增 from 病例基本信息 where 日期>='2021-1-1' and 日期<'2021-2-1' group by 市)
where 新增 >= all(select count(*) from 病例基本信息 where 日期>='2021-1-1' and 日期<'2021-2-1' group by 市)

结果：石家庄市

方法三（简单但效率较低）：排序取第一

select 市, count(*) as 新增 from 病例基本信息 where 日期>='2021-1-1' and 日期<'2021-2-1' group by 市 order by 新增 desc limit 1

结果：石家庄市

3-6 查询除中美两国以外的其余国家中，进入2021年以来单日新增确诊病例始终不低于一万例的国家。

查询：select 国家, 日期, sum(累计确诊) from 各国疫情数据统计 group by 国家,日期查询每个国家每一天的累计确诊。为了得到单日新增，将这个子查询复制两份，分别为A表与B表，按照国家做连接。A.日期-B.日期 = 1的含义是把A表当做每一天，B表当做每一天的前一天，按照国家分组后，A.sum(累计确诊) - B.sum(累计确诊)即可得到每天的新增，min(A.sum - B.sum) >= 10000即可筛选得到单日新增不低于一万的国家。

select A.国家
from (select 国家, 日期, sum(累计确诊) from 各国疫情数据统计 group by 国家,日期) as A 
join (select 国家, 日期, sum(累计确诊) from 各国疫情数据统计 group by 国家,日期) as B 
on A.国家 = B.国家 and A.日期-B.日期 = 1 
where A.国家 != 'US' and A.日期 >= '2021-01-01'
group by A.国家
having min(A.sum - B.sum) >= 10000

-- 使用with简化
with subtable as (select 国家, 日期, sum(累计确诊) from 各国疫情数据统计 group by 国家,日期)
select A.国家
from subtable as A join subtable as B 
on A.国家 = B.国家 and A.日期-B.日期 = 1 
where A.国家 != 'US' and A.日期 >= '2021-01-01'
group by A.国家
having min(A.sum - B.sum) >= 10000

结果：（截图分别为不使用with与使用with）

	国家
1	`Russia`
2	`United Kingdom`
3	`Brazil`

查看评论 - 2 条评论

Comments 2 条评论

博主 fircas

回复

发布于 2023-12-27 22:56 ( Edge 120 Windows 10/11 )( ) 来自: 中国 · 北京市 · 北京

请问有剩下的实验报告吗
- 博主 Sinkers
  
  回复
  
  发布于 2024-01-25 09:20 ( Edge 120 Windows 10/11 )( ) 来自: 中国 · 江苏省 · 苏州市
  
  @fircas 应该有哈，不知道你想要的哪个