选读SQL经典实例笔记17_最多和最少

1. 问题4

1.1. 最多选修两门课程的学生，没有选修任何课程的学生应该被排除在外

1.2. sql

1. select distinct s.*
2.   from student s, take t
3. where s.sno = t.sno
4.    and s.sno not in ( select t1.sno
5.                         from take t1, take t2, take t3
6.                        where t1.sno = t2.sno
7.                          and t2.sno = t3.sno
8.                          and t1.cno ＜ t2.cno
9.                          and t2.cno ＜ t3.cno )

复制代码

1.3. 两次自连接的解决方案避免了聚合运算

1.4. 基于SNO的内连接操作能够确保子查询返回的每一行都是针对同一个学生的数据

1.5. 子查询就是为了找出选修了3门以上课程的学生

1.6. 外层查询则负责返回至少选修了一门课程，并且SNO不存在于子查询返回结果的学生

1.7. DB2

1.8. Oracle

1.9. SQL Server

1.10. 窗口函数COUNT OVER

1.10.1.   sql

1. select distinct sno,sname,age
2.     from (
3.   select s.sno,s.sname,s.age,
4.          count(*) 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 ＜= 2

复制代码

1.11. PostgreSQL

1.12. MySQL

1.13. 聚合函数COUNT判断哪些学生最多选修了两门课程

1.13.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 count(*) ＜= 2

复制代码

1.14. 计算出TAKE表中每个SNO出现的次数

1.15. STUDENT表和TAKE表的内连接操作能够确保剔除掉没有选修任何课程的学生

2. 问题5

2.1. 年龄最多大于其他两名同学的学生

2.1.1. 比其他0个、1个或者2个学生年龄大的学生

2.2. sql

1. select *
2.   from student
3. where sno not in (
4. select s1.sno
5.   from student s1,
6.        student s2,
7.        student s3,
8.        student s4
9. where s1.age ＞ s2.age
10.    and s2.age ＞ s3.age
11.    and s3.age ＞ s4.age
12. )
13. SNO SNAME      AGE
14. --- ---------- ---
15.   6 JING        18
16.   4 MAGGIE      19
17.   1 AARON       20
18.   9 GILLIAN     20
19.   8 KAY         20
20.   3 DOUG        20

复制代码

2.3. 找出比其他3个或更多学生年龄大的学生集合

2.3.1. 大于具有传递性

2.4. 为提高可读性，使用DISTINCT压缩结果集

2.5. 在子查询中使用NOT IN就可以筛选出除了上述4人之外的那些学生

2.6. DB2

2.7. Oracle

2.8. SQL Server

2.9. 窗口函数DENSE_RANK

2.9.1.  sql

1. select sno,sname,age
2.    from (
3. select sno,sname,age,
4.         dense_rank()over(order by age) as dr
5.    from student
6.         ) x
7.   where dr ＜= 3

复制代码

2.10. 窗口函数DENSE_RANK根据有多少人比当前学生年龄小计算出每个学生对应的排名

2.11. DENSE_RANK不仅允许Tie的存在，还能保证名次连续，中间不留空白

2.12. PostgreSQL

2.13. MySQL

2.14. 聚合函数COUNT和关联子查询

2.14.1.  sql

1. select s1.*
2.    from student s1
3.   where 2 ＞= ( select count(*)
4.                 from student s2
5.                where s2.age ＜s1.age )

复制代码

2.15. 聚合函数解决方案使用标量子查询筛选出最多比其他两名学生年龄大的学生

3. 问题6

3.1. 至少选修了两门课程的学生

3.2. sql

1. select *
2.   from student
3. where sno in (
4. select t1.sno
5. from take t1,
6.       take t2
7. where t1.sno = t2.sno
8.    and t1.cno ＞ t2.cno
9. )
10. SNO SNAME             AGE
11. --- ---------- ----------
12.   1 AARON              20
13.   3 DOUG               20
14.   4 MAGGIE             19
15.   6 JING               18

复制代码

3.3. 子查询里的SNO相等条件能够确保每个学生只与自己的选课信息相比较

3.4. CNO大于比较条件，只有在一个学生至少选修了一门课程的情况下才会成立，否则CNO会等于另一个CNO

3.4.1. 只有一门课程，只能和自身比较

3.5. DB2

3.6. Oracle

3.7. SQL Server

3.8. 窗口函数COUNT OVER

3.8.1.  sql

1. select distinct sno,sname,age
2.    from (
3. select s.sno,s.sname,s.age,
4.         count(*) 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 ＞= 2

复制代码

3.9. 使用STUDENT表的全部列定义分区并执行COUNT OVER操作

3.10. 只要保留那些CNT大于或者等于2的行即可

3.11. PostgreSQL

3.12. MySQL

3.13. 聚合函数COUNT

3.13.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 count(*) ＞= 2

复制代码

3.14. HAVING子句中使用COUNT筛选出那些选修了两门以上课程的学生

4. 问题7

4.1. 同时选修了CS112和CS114两门课程的学生

4.2. sql

1. select s.*
2.   from student s,
3.        take t1,
4.        take t2
5. where s.sno = t1.sno
6.    and t1.sno = t2.sno
7.    and t1.cno = 'CS112'
8.    and t2.cno = 'CS114'
9. SNO SNAME       AGE
10. --- ---------- ----
11.   1 AARON        20
12.   3 DOUG         20

复制代码

4.3. sql

1. select s.*
2.   from take t1, student s
3. where s.sno   = t1.sno
4.    and t1.cno  = 'CS114'
5.    and 'CS112' = any (select t2.cno
6.                         from take t2
7.                        where t1.sno = t2.sno
8.                          and t2.cno != 'CS114')
9. SNO SNAME       AGE
10. --- ---------- ----
11.   1 AARON        20
12.   3 DOUG         20

复制代码

4.4. DB2

4.5. Oracle

4.6. SQL Server

4.7. 窗口函数MIN OVER和MAX OVER

4.7.1.  sql

1. select distinct sno, sname, age
2.    from (
3. select s.sno, s.sname, s.age,
4.         min(cno) over (partition by s.sno) as min_cno,
5.         max(cno) over (partition by s.sno) as max_cno
6.    from student s, take t
7.   where s.sno = t.sno
8.     and t.cno in ('CS114','CS112')
9.         ) x
10.   where min_cno != max_cno

复制代码

4.8. PostgreSQL

4.9. MySQL

4.10. 聚合函数MIN和MAX

4.10.1.  sql

1. select s.sno, s.sname, s.age
2.    from student s, take t
3.   where s.sno = t.sno
4.     and t.cno in ('CS114','CS112')
5.   group by s.sno, s.sname, s.age
6. having min(t.cno) != max(t.cno)

复制代码

4.11. IN列表确保只有选修CS112或CS114，或者同时两门都选了的学生才会被保留下来

4.12. 如果一个学生没有同时选修这两门课程，那么MIN(CNO)就会等于MAX(CNO)，进而该学生会被排除在外

5. 问题8

5.1. 至少比其他两位学生年龄大的学生

5.2. sql

1. select distinct s1.*
2.   from student s1,
3.        student s2,
4.        student s3
5. where s1.age ＞ s2.age
6.    and s2.age ＞ s3.age
7. SNO SNAME             AGE
8. --- ---------- ----------
9.   1 AARON              20
10.   2 CHUCK              21
11.   3 DOUG               20
12.   5 STEVE              22
13.   7 BRIAN              21
14.   8 KAY                20
15.   9 GILLIAN            20
16. 10 CHAD               21

复制代码

5.3. DB2

5.4. Oracle

5.5. SQL Server

5.6. 窗口函数DENSE_RANK

5.6.1.  sql

1. select sno,sname,age
2.    from (
3. select sno,sname,age,
4.         dense_rank()over(order by age) as dr
5.    from student
6.         ) x
7.   where dr ＞= 3

复制代码

5.7. PostgreSQL

5.8. MySQL

5.9. 聚合函数COUNT和关联子查询

5.9.1.  sql

1. select s1.*
2.    from student s1
3.   where 2 ＜= ( select count(*)
4.                 from student s2
5.                where s2.age ＜s1.age )

复制代码

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