选读SQL经典实例笔记16_逻辑否定

1. 示例数据

1.1. student

1. insert into student values (1,'AARON',20)
2. insert into student values (2,'CHUCK',21)
3. insert into student values (3,'DOUG',20)
4. insert into student values (4,'MAGGIE',19)
5. insert into student values (5,'STEVE',22)
6. insert into student values (6,'JING',18)
7. insert into student values (7,'BRIAN',21)
8. insert into student values (8,'KAY',20)
9. insert into student values (9,'GILLIAN',20)
10. insert into student values (10,'CHAD',21)

复制代码

1.2. courses

1. insert into courses values ('CS112','PHYSICS',4)
2. insert into courses values ('CS113','CALCULUS',4)
3. insert into courses values ('CS114','HISTORY',4)

复制代码

1.3. professor

1. insert into professor values ('CHOI','SCIENCE',400,45)
2. insert into professor values ('GUNN','HISTORY',300,60)
3. insert into professor values ('MAYER','MATH',400,55)
4. insert into professor values ('POMEL','SCIENCE',500,65)
5. insert into professor values ('FEUER','MATH',400,40)

复制代码

1.4. take

1. insert into take values (1,'CS112')
2. insert into take values (1,'CS113')
3. insert into take values (1,'CS114')
4. insert into take values (2,'CS112')
5. insert into take values (3,'CS112')
6. insert into take values (3,'CS114')
7. insert into take values (4,'CS112')
8. insert into take values (4,'CS113')
9. insert into take values (5,'CS113')
10. insert into take values (6,'CS113')
11. insert into take values (6,'CS114')

复制代码

1.5. teach

1. insert into teach values ('CHOI','CS112')
2. insert into teach values ('CHOI','CS113')
3. insert into teach values ('CHOI','CS114')
4. insert into teach values ('POMEL','CS113')
5. insert into teach values ('MAYER','CS112')
6. insert into teach values ('MAYER','CS114')

复制代码

2. 问题1：没有选修过CS112课程的学生

2.1. sql

1. select *
2.   from student
3. where sno in ( select sno
4.                   from take
5.                  where cno != 'CS112' )

复制代码

2.2. sql

1. select *
2.   from student
3. where sno not in (select sno
4.                      from take
5.                     where cno = 'CS112')

复制代码

2.3. 要记住真正的逻辑否定要求两个步骤，即为了找出‘哪些人不是’，就要先找出‘哪些人是’，然后再排除掉他们

2.4. PostgreSQL

2.5. MySQL

2.6. 使用CASE表达式和聚合函数MAX标识一个学生是否选修了CS112课程

2.6.1. sql

1. select s.sno,s.sname,s.age
2.   from student s left join take t
3.     on (s.sno = t.sno)
4. group by s.sno,s.sname,s.age
5. having max(case when t.cno = 'CS112'
6.                 then 1 else 0 end) = 0

复制代码

2.7. Oracle

2.7.1. group by解决方案

1. select s.sno,s.sname,s.age
2.   from student s, take t
3.  where s.sno = t.sno (+)
4.  group by s.sno,s.sname,s.age
5. having max(case when t.cno = 'CS112'
6.                 then 1 else 0 end) = 0

复制代码

2.7.2. 窗口函数解决方案

1. select distinct sno,sname,age
2.   from (
3. select s.sno,s.sname,s.age,
4.        max(case when t.cno = 'CS112'
5.                 then 1 else 0 end)
6.        over(partition by s.sno,s.sname,s.age) as takes_CS112
7.   from student s, take t
8.  where s.sno = t.sno (+)
9.        ) x
10.  where takes_CS112 = 0

复制代码

2.8. DB2

2.9. SQL Server

2.10. 使用CASE表达式和窗口函数MAX OVER

2.10.1. sql

1. select distinct sno,sname,age
2.   from (
3. select s.sno,s.sname,s.age,
4.        max(case when t.cno = 'CS112'
5.                 then 1 else 0 end)
6.        over(partition by s.sno,s.sname,s.age) as takes_CS112
7.   from student s, take t
8.     on (s.sno = t.sno)
9.        ) x
10. where takes_CS112 = 0

复制代码

2.11. 外连接到TAKE表是为了确保把那些没有选修任何课程的学生也能被筛选出来

2.12. 调用MAX函数找出最大的CASE表达式返回值

3. 问题2：只选修了CS112和CS114中的一门，而不是两门都选的学生

3.1. sql

1. select *
2.   from student
3. where sno in ( select sno
4.                   from take
5.                  where cno != 'CS112'
6.                    and cno != 'CS114' )

复制代码

3.2. sql

1. select *
2.   from student s, take t
3. where s.sno = t.sno
4.    and t.cno in ( 'CS112', 'CS114' )
5.    and s.sno not in ( select a.sno
6.                       from take a, take b
7.                      where a.sno = b.sno
8.                        and a.cno = 'CS112'
9.                        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

1. select distinct sno,sname,age
2.   from (
3. select s.sno,s.sname,s.age,
4.        sum(case when t.cno in ('CS112','CS114') then 1 else 0 end)
5.        over (partition by s.sno,s.sname,s.age) as takes_either_or
6.   from student s, take t
7. where s.sno = t.sno
8.        )x
9. where takes_either_or = 1

复制代码

3.9. PostgreSQL

3.10. MySQL

3.11. CASE表达式和聚合函数SUM

3.11.1. sql

1. select s.sno,s.sname,s.age
2.   from student s, take t
3. where s.sno = t.sno
4. group by s.sno,s.sname,s.age
5. having sum(case when t.cno in ('CS112','CS114')
6.                 then 1 else 0 end) = 1

复制代码

3.12. 内连接STUDENT表和TAKE表，这样就排除了那些没有选修任何课程的学生

3.13. 使用CASE表达式标记一个学生是否选修了这两门课程中的一门

3.14. 函数SUM会把每个学生对应的1都累加起来

4. 问题3:选修了CS112，而且没有选修其他课程的学生

4.1. sql

1. select s.*
2.   from student s, take t
3. where s.sno = t.sno
4.    and t.cno = 'CS112'

复制代码

4.2. sql

1. select s.*
2.   from student s, take t
3. where s.sno = t.sno
4.    and s.sno not in ( select sno
5.                         from take
6.                        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

1. select s.*
2.   from student s,
3.        take t1,
4.        (
5. select sno
6.    from take
7. group by sno
8. having count(*) = 1
9.        ) t2
10. where s.sno  = t1.sno
11.    and t1.sno = t2.sno
12.    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

1. select sno,sname,age
2.   from (
3. select s.sno,s.sname,s.age,t.cno,
4.        count(t.cno) over (
5.          partition by s.sno,s.sname,s.age
6.        ) as cnt
7.   from student s, take t
8. where s.sno = t.sno
9.        ) x
10. where cnt = 1
11.    and cno = 'CS112'

复制代码

4.12.2. 窗口函数解决方案处理方式上稍有不同（更有效率）

4.12.3. 内嵌视图X返回了每一个学生、他们选修的课程以及他们选修了几门课程

4.12.4. 获得了每个学生选修的课程和课程数目之后，最后只要保留CNT等于1并且CNO等于CS112的行即可


免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！