选读SQL经典实例笔记16_逻辑否定
1.选读SQL经典实例笔记23_读后总结与感想兼导读2.选读SQL经典实例笔记01_检索和排序3.选读SQL经典实例笔记02_多表查询4.选读SQL经典实例笔记03_DML和元数据5.选读SQL经典实例笔记04_日期运算(上)6.选读SQL经典实例笔记05_日期运算(下)7.选读SQL经典实例笔记06_日期处理(上)8.选读SQL经典实例笔记07_日期处理(下)9.选读SQL经典实例笔记08_区间查询10.选读SQL经典实例笔记09_数值处理11.选读SQL经典实例笔记10_高级查询12.选读SQL经典实例笔记11_结果集变换13.选读SQL经典实例笔记12_桶、图和小计14.选读SQL经典实例笔记13_case与聚合15.选读SQL经典实例笔记14_层次查询16.选读SQL经典实例笔记15_窗口函数
17.选读SQL经典实例笔记16_逻辑否定
18.选读SQL经典实例笔记17_最多和最少19.选读SQL经典实例笔记18_Exactly20.选读SQL经典实例笔记19_Any和All21.选读SQL经典实例笔记20_Oracle语法示例22.选读SQL经典实例笔记21_字符串处理23.选读SQL经典实例笔记22_2版增补1. 示例数据
1.1. student
insert into student values (1,'AARON',20)
insert into student values (2,'CHUCK',21)
insert into student values (3,'DOUG',20)
insert into student values (4,'MAGGIE',19)
insert into student values (5,'STEVE',22)
insert into student values (6,'JING',18)
insert into student values (7,'BRIAN',21)
insert into student values (8,'KAY',20)
insert into student values (9,'GILLIAN',20)
insert into student values (10,'CHAD',21)
1.2. courses
insert into courses values ('CS112','PHYSICS',4)
insert into courses values ('CS113','CALCULUS',4)
insert into courses values ('CS114','HISTORY',4)
1.3. professor
insert into professor values ('CHOI','SCIENCE',400,45)
insert into professor values ('GUNN','HISTORY',300,60)
insert into professor values ('MAYER','MATH',400,55)
insert into professor values ('POMEL','SCIENCE',500,65)
insert into professor values ('FEUER','MATH',400,40)
1.4. take
insert into take values (1,'CS112')
insert into take values (1,'CS113')
insert into take values (1,'CS114')
insert into take values (2,'CS112')
insert into take values (3,'CS112')
insert into take values (3,'CS114')
insert into take values (4,'CS112')
insert into take values (4,'CS113')
insert into take values (5,'CS113')
insert into take values (6,'CS113')
insert into take values (6,'CS114')
1.5. teach
insert into teach values ('CHOI','CS112')
insert into teach values ('CHOI','CS113')
insert into teach values ('CHOI','CS114')
insert into teach values ('POMEL','CS113')
insert into teach values ('MAYER','CS112')
insert into teach values ('MAYER','CS114')
2. 问题1:没有选修过CS112课程的学生
2.1. sql
select *
from student
where sno in ( select sno
from take
where cno != 'CS112' )
2.2. sql
select *
from student
where sno not in (select sno
from take
where cno = 'CS112')
2.3. 要记住真正的逻辑否定要求两个步骤,即为了找出‘哪些人不是’,就要先找出‘哪些人是’,然后再排除掉他们
2.4. PostgreSQL
2.5. MySQL
2.6. 使用CASE表达式和聚合函数MAX标识一个学生是否选修了CS112课程
2.6.1. sql
select s.sno,s.sname,s.age
from student s left join take t
on (s.sno = t.sno)
group by s.sno,s.sname,s.age
having max(case when t.cno = 'CS112'
then 1 else 0 end) = 0
2.7. Oracle
2.7.1. group by解决方案
select s.sno,s.sname,s.age
from student s, take t
where s.sno = t.sno (+)
group by s.sno,s.sname,s.age
having max(case when t.cno = 'CS112'
then 1 else 0 end) = 0
2.7.2. 窗口函数解决方案
select distinct sno,sname,age
from (
select s.sno,s.sname,s.age,
max(case when t.cno = 'CS112'
then 1 else 0 end)
over(partition by s.sno,s.sname,s.age) as takes_CS112
from student s, take t
where s.sno = t.sno (+)
) x
where takes_CS112 = 0
2.8. DB2
2.9. SQL Server
2.10. 使用CASE表达式和窗口函数MAX OVER
2.10.1. sql
select distinct sno,sname,age
from (
select s.sno,s.sname,s.age,
max(case when t.cno = 'CS112'
then 1 else 0 end)
over(partition by s.sno,s.sname,s.age) as takes_CS112
from student s, take t
on (s.sno = t.sno)
) x
where takes_CS112 = 0
2.11. 外连接到TAKE表是为了确保把那些没有选修任何课程的学生也能被筛选出来
2.12. 调用MAX函数找出最大的CASE表达式返回值
3. 问题2:只选修了CS112和CS114中的一门,而不是两门都选的学生
3.1. sql
select *
from student
where sno in ( select sno
from take
where cno != 'CS112'
and cno != 'CS114' )
3.2. sql
select *
from student s, take t
where s.sno = t.sno
and t.cno in ( 'CS112', 'CS114' )
and s.sno not in ( select a.sno
from take a, take b
where a.sno = b.sno
and a.cno = 'CS112'
and b.cno = 'CS114' )
3.3. 使用自连接找出同时选修了CS112和CS114的学生
3.4. 使用子查询从选修了CS112或CS114的学生中把同时选了两门的学生剔除掉
3.5. DB2
3.6. Oracle
3.7. SQL Server
3.8. CASE表达式和窗口函数SUM OVER
3.8.1. sql
select distinct sno,sname,age
from (
select s.sno,s.sname,s.age,
sum(case when t.cno in ('CS112','CS114') then 1 else 0 end)
over (partition by s.sno,s.sname,s.age) as takes_either_or
from student s, take t
where s.sno = t.sno
)x
where takes_either_or = 1
3.9. PostgreSQL
3.10. MySQL
3.11. CASE表达式和聚合函数SUM
3.11.1. sql
select s.sno,s.sname,s.age
from student s, take t
where s.sno = t.sno
group by s.sno,s.sname,s.age
having sum(case when t.cno in ('CS112','CS114')
then 1 else 0 end) = 1
3.12. 内连接STUDENT表和TAKE表,这样就排除了那些没有选修任何课程的学生
3.13. 使用CASE表达式标记一个学生是否选修了这两门课程中的一门
3.14. 函数SUM会把每个学生对应的1都累加起来
4. 问题3:选修了CS112,而且没有选修其他课程的学生
4.1. sql
select s.*
from student s, take t
where s.sno = t.sno
and t.cno = 'CS112'
4.2. sql
select s.*
from student s, take t
where s.sno = t.sno
and s.sno not in ( select sno
from take
where cno != 'CS112' )
4.3. 子查询负责找出至少选修了一门课,但又没有选修CS112的所有学生
4.4. 外层查询负责找出选修了一门课程(任意课程),并且不在上述子查询的返回结果的学生
4.5. STUDENT表和TAKE表之间的连接操作过滤掉没有选修任何课程的学生
4.6. PostgreSQL
4.7. MySQL
4.8. 使用聚合函数COUNT确保下列查询返回的学生只选修了一门课程
4.8.1. sql
select s.*
from student s,
take t1,
(
select sno
from take
group by sno
having count(*) = 1
) t2
where s.sno = t1.sno
and t1.sno = t2.sno
and t1.cno = 'CS112'
4.8.2. 使用内嵌视图T2找出只选修了一门课程的学生
4.8.3. 连接内嵌视图T2到TAKE表,并且筛选出选修CS112课程的学生
4.8.4. 在内嵌视图T2和TAKE表连接查询的基础上再次连接STUDENT表,找出匹配的学生
4.9. DB2
4.10. Oracle
4.11. SQL Server
4.12. 使用窗口函数COUNT OVER
4.12.1. sql
select sno,sname,age
from (
select s.sno,s.sname,s.age,t.cno,
count(t.cno) over (
partition by s.sno,s.sname,s.age
) as cnt
from student s, take t
where s.sno = t.sno
) x
where cnt = 1
and cno = 'CS112'
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 【.NET】调用本地 Deepseek 模型
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· 我与微信审核的“相爱相杀”看个人小程序副业