Mysql｜数据分析搞懂这15道SQL题目笔试就稳了

现有以下三张表

写出SQL语句：查询产品名称=“A药品”，在北京医院2018~2019两年的销售“金额”，排除两年销售金额总和>1000000的医院，要求查询结果如下表。

写出SQL语句，查询题1的销量表中2019年任意连续三个月销售额都>0的医院。

返回字段：HospitalId，SalesMonth(2019年销量>0的所有月份，逗号隔开)

以下是微信聊天记录表

写出SQL语句：按月统计2020年的微信回复率

发送次数 = 一组好友在一个自然天内的所有发送记录计为1次。

回复率计算公式 =（发送次数在两个自然天内被回复）/发送次数*100%

未完。。。 持续更新中。。。

/*

闲着没事，瞅瞅百度上的问题，今天天晚了，先解决一个，另一个明儿个再说了！

第二道题也算已经搞定了！

环境 : mysql Ver 14.12 Distrib 5.0.45, for Win32 (ia32)

参考 :

exist与in 的区别

http://blog.csdn.net/change888/archive/2008/03/31/2232778.aspx

*/

/*********************************问题 1 **************************************/

drop table if exists s

create table if not exists s (s varchar(32), sn varchar(32), sd varchar(32),

sa int)

insert into s values ('s1', '朱', '开发本部', 23)

insert into s values ('s2', '牛', '人事部', 25)

insert into s values ('s3', '杨', '财务部', 26)

insert into s values ('s4', '马', '开发本部', 22)

insert into s values ('s5', '吕', '人事部', 27)

insert into s values ('s6', '于', '开发本部', 28)

insert into s values ('s7', '侯', '开发本部', 28)

drop table if exists c

create table if not exists c (c varchar(32), cn varchar(32))

insert into c values ('c1', '软件工程')

insert into c values ('c2', '计算机技术与科学')

insert into c values ('c3', '车辆工程')

drop table if exists sc

create table if not exists sc (s varchar(32), c varchar(32))

insert into sc values ('s1', 'c1')

insert into sc values ('s1', 'c2')

insert into sc values ('s1', 'c3')

insert into sc values ('s2', 'c1')

insert into sc values ('s2', 'c3')

insert into sc values ('s3', 'c2')

insert into sc values ('s4', 'c2')

insert into sc values ('s4', 'c3')

insert into sc values ('s5', 'c1')

insert into sc values ('s6', 'c3')

/* 1. 查询选修课程名称为 “软件工程” 的学员学号和姓名 */

select s.s '学号', s.sn '姓名' from s where s.s in

(select sc.s from sc where sc.c in

(select c.c from c where c.cn = '软件工程'))

/* 2. 查询选修课程编号为 “C2” 的学员姓名和所属单位 */

select s.sn '姓名', s.sd '所属单位' from s where s.s in

(select sc.s from sc where sc.c = 'C2')

/* 3. 查询选修课程编号 不 为 “C2” 的学员姓名和所属单位 */

select s.sn '姓名', s.sd '所属单位' from s where

s.s not in (select sc.s from sc where sc.c = 'C2')

and

s.s in (select sc.s from sc)

/* 4. 查询选修全部课程的学员姓名和所属单位 */

select s.sn '姓名', s.sd '所属单位' from s where

(select count(DISTINCT sc.c) from sc where sc.s = s.s)

=

(select count(DISTINCT c.c) from c )

/* 5. 查询选修了课程的学员人数 */

select count(DISTINCT sc.s) '人数' from sc

/* 6. 查询选修课程 >= 2 门的学员学号和所属单位 （不得不用 CASE 语句了）*/

select s.sn '姓名', s.sd '所属单位' from s where s.s in

(select CASE WHEN count(DISTINCT sc.c) >=2 THEN sc.s END from sc group by sc.s )

/* 运行结果

------------------------------------1

+------+------+

| 学号 | 姓名 |

+------+------+

| s1 | 朱 |

| s2 | 牛 |

| s5 | 吕 |

+------+------+

------------------------------------2

+------+----------+

| 姓名 | 所属单位 |

+------+----------+

| 朱 | 开发本部 |

| 杨 | 财务部 |

| 马 | 开发本部 |

+------+----------+

------------------------------------3

+------+----------+

| 姓名 | 所属单位 |

+------+----------+

| 牛 | 人事部 |

| 吕 | 人事部 |

| 于 | 开发本部 |

+------+----------+

------------------------------------4

+------+----------+

| 姓名 | 所属单位 |

+------+----------+

| 朱 | 开发本部 |

+------+----------+

------------------------------------5

+------+

| 人数 |

+------+

|6 |

+------+

------------------------------------6

+------+----------+

| 姓名 | 所属单位 |

+------+----------+

| 朱 | 开发本部 |

| 牛 | 人事部 |

| 马 | 开发本部 |

+------+----------+

*/

/*********************************问题 2 **************************************/

drop table if exists s

create table if not exists s ( sno varchar(32), sname varchar(32))

insert into s values ('s1', '朱')

insert into s values ('s2', '牛')

insert into s values ('s3', '杨')

insert into s values ('s4', '马')

insert into s values ('s5', '吕')

insert into s values ('s6', '于')

insert into s values ('s7', '侯')

drop table if exists c

create table if not exists c ( cno varchar(32), cname varchar(32),

cteacher varchar(32))

insert into c values ('c1', '数学', '张')

insert into c values ('c2', '日语', '李')/*假设李老师同时教授日语和英语*/

insert into c values ('c3', '英语', '李')

drop table if exists sc

create table if not exists sc (sno varchar(32), cno varchar(32),

scgrade double)

insert into sc values ('s1', 'c1', 75)

insert into sc values ('s1', 'c2', 70)

insert into sc values ('s1', 'c3', 80)

insert into sc values ('s2', 'c1', 50)

insert into sc values ('s2', 'c3', 40)

insert into sc values ('s3', 'c1', 50)

insert into sc values ('s3', 'c2', 60)

insert into sc values ('s4', 'c1', 90)

insert into sc values ('s4', 'c2', 40)

insert into sc values ('s4', 'c3', 20)

insert into sc values ('s5', 'c1', 80)

insert into sc values ('s6', 'c1', 85)

/* 1. 没有 选 修过“李”老师讲授课程的所有学生姓名 */

select s.sname '姓名' from s where s.sno not in

(select sc.sno from sc where sc.cno in

(select c.cno from c where c.cteacher = '李'))

/* 2. 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩 */

select s.sname '姓名', AVG(sc.scgrade) '平均成绩' from s, sc

where s.sno = sc.sno

and

(select count(sc.sno) from sc where sc.sno = s.sno

and sc.scgrade <60 ) >= 2

group by s.sno

/* 3. 列出既学过“C1”号课程，又学过“C2”号课程的所有学生姓名 */

select s.sname '姓名' from s where s.sno in

(select t1.sno from sc t1, sc t2

where t1.sno = t2.sno and t1.cno = 'c1' and t2.cno = 'c2')

/*或者*/

select s.sname '姓名' from s where s.sno in

(select sc.sno from sc where sc.cno = 'c1' and sc.sno in

(select t1.sno from sc t1 where t1.cno = 'c2'))

/* 4. 列出“C1”号课成绩比“C2”号同学该门课成绩高的所有学生的学号 */

select t1.sno '学号' from sc t1, sc t2

where t1.sno = t2.sno and t1.cno = 'c1'

and t2.cno = 'c2' and t1.scgrade >t2.scgrade

/* 5. 列出“C1”成绩比“C2”成绩高的学生的学号及其“C1”和“C2”的成绩 */

select t1.sno '学号', t1.scgrade 'C1成绩', t2.scgrade 'C2成绩' from sc t1, sc t2

where t1.sno = t2.sno and t1.cno = 'c1'

and t2.cno = 'c2' and t1.scgrade >t2.scgrade

/* 运行结果

------------------------------------1

+------+

| 姓名 |

+------+

| 吕 |

| 于 |

| 侯 |

+------+

------------------------------------2

+------+----------+

| 姓名 | 平均成绩 |

+------+----------+

| 牛 | 45 |

| 马 | 50 |

+------+----------+

------------------------------------3

+------+

| 姓名 |

+------+

| 朱 |

| 杨 |

| 马 |

+------+

------------------------------------4

+------+

| 学号 |

+------+

| s1 |

| s4 |

+------+

------------------------------------5

+------+--------+--------+

| 学号 | C1成绩 | C2成绩 |

+------+--------+--------+

| s1 | 75 | 70 |

| s4 | 90 | 40 |

+------+--------+--------+

*/

select (case when f_type='lack1' then f_name else '' end) as f_name_lack1, (case when f_type='lack1' then f_value else '' end) as f_value_lack1,(case when f_type='lack2' then f_name else '' end) as f_name_lack2, (case when f_type='lack2' then f_value else ''end) as f_value_lack2, (case when f_type='lack3' then f_name else '' end) as f_name_lack3, (case when f_type='lack3' then f_value else '' end) as f_value_lack3 from table_a where 1

---