示例表和数据

  1. /* student表*/
  2. create table student
  3. ( sno integer,
  4. sname varchar(10),
  5. age integer
  6. );
  8. /* courses表*/
  9. create table courses
  10. ( cno varchar(5),
  11. title varchar(10),
  12. credits integer
  13. );
  14. /* professor表 */
  15. create table professor
  16. ( lname varchar(10),
  17. dept varchar(10),
  18. salary integer,
  19. age integer
  20. );
  21. /* student表和学生选修的课程*/
  22. create table take
  23. ( sno integer,
  24. cno varchar(5)
  25. );
  26. /* professor表和教授所讲的课程*/
  27. create table teach
  28. ( lname varchar(10),
  29. cno varchar(5)
  30. );
  31. TRUNCATE table student;
  33. insert into student values (1,'AARON',20);
  34. insert into student values (2,'CHUCK',21);
  35. insert into student values (3,'DOUG',20);
  36. insert into student values (4,'MAGGIE',19)                 ;
  37. insert into student values (5,'STEVE',22)               ;
  38. insert into student values (6,'JING',18)                ;
  39. insert into student values (7,'BRIAN',21)               ;
  40. insert into student values (8,'KAY',20)                 ;
  41. insert into student values (9,'GILLIAN',20)             ;
  42. insert into student values (10,'CHAD',21)               ;
  43. insert into courses values ('CS112','PHYSICS',4)        ;
  44. insert into courses values ('CS113','CALCULUS',4)       ;
  45. insert into courses values ('CS114','HISTORY',4)        ;
  46. insert into professor values ('CHOI','SCIENCE',400,45)  ;
  47. insert into professor values ('GUNN','HISTORY',300,60)  ;
  48. insert into professor values ('MAYER','MATH',400,55)    ;
  49. insert into professor values ('POMEL','SCIENCE',500,65) ;
  50. insert into professor values ('FEUER','MATH',400,40)    ;
  51. insert into take values (1,'CS112')                     ;
  52. insert into take values (1,'CS113')                     ;
  53. insert into take values (1,'CS114')                     ;
  54. insert into take values (2,'CS112')                     ;
  55. insert into take values (3,'CS112')                     ;
  56. insert into take values (3,'CS114')                     ;
  57. insert into take values (4,'CS112')                     ;
  58. insert into take values (4,'CS113')                     ;
  59. insert into take values (5,'CS113')                     ;
  60. insert into take values (6,'CS113')                     ;
  61. insert into take values (6,'CS114')                     ;
  62. insert into teach values ('CHOI','CS112')               ;
  63. insert into teach values ('CHOI','CS113')               ;
  64. insert into teach values ('CHOI','CS114')               ;
  65. insert into teach values ('POMEL','CS113')              ;
  66. insert into teach values ('MAYER','CS112')              ;
  67. insert into teach values ('MAYER','CS114')              ;

B.2 逻辑否定问题

B.2.2 问题1

找出没有选修过CS112课程的学生。但下面的查询语句返回的结果是错的。

  1. SELECT
  2.     * 
  3. FROM
  4.     student 
  5. WHERE
  6.     sno IN (SELECT sno FROM take WHERE cno != 'CS112' ) ;

一个学生可能会选修多门课程，而以上查询却有可能把选修了CS112的学生也提取出来。该查询之所以不正确，就是因为他没有正确回答：“谁没有选修CS112？”实际上，它回答的问题是：“谁选修了CS112之外的课程”。正确的结果集应该包括没有选修任何课程的学生，以及选修了一些课程却没有选修CS112的学生。最终，你希望得到如下的结果集。

  1. SNO SNAME AGE
  2. --------- ---------- ----------
  3. 5     STEVE     22
  4. 6     JING         18
  5. 7     BRIAN         21
  6. 8     KAY         20
  7. 9     GILLIAN     20
  8. 10     CHAD         21

MySQL

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

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

或者使用窗口函数MAX OVER解决

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

讨论

上述做法，是在结果集中创建一个布尔列来表示学生是否选修了CS112课程。如果一个学生选修了CS112，那么该列的值为1，否则为0。

  1. mysql> select s.sno,
  2.                s.sname,
  3.                s.age,
  4.                max(case when t.cno = 'CS112' then 1 else  0 end) over(partition by s.sno, s.sname, s.age) as takes_CS112
  5.           from student s LEFT JOIN take t
  6.             on (s.sno = t.sno);
  7. +-----+---------+-----+-------------+
  8. | sno | sname   | age | takes_CS112 |
  9. +-----+---------+-----+-------------+
  10. |   1 | AARON   |  20 |           1 |
  11. |   1 | AARON   |  20 |           1 |
  12. |   1 | AARON   |  20 |           1 |
  13. |   2 | CHUCK   |  21 |           1 |
  14. |   3 | DOUG    |  20 |           1 |
  15. |   3 | DOUG    |  20 |           1 |
  16. |   4 | MAGGIE  |  19 |           1 |
  17. |   4 | MAGGIE  |  19 |           1 |
  18. |   5 | STEVE   |  22 |           0 |
  19. |   6 | JING    |  18 |           0 |
  20. |   6 | JING    |  18 |           0 |
  21. |   7 | BRIAN   |  21 |           0 |
  22. |   8 | KAY     |  20 |           0 |
  23. |   9 | GILLIAN |  20 |           0 |
  24. |  10 | CHAD    |  21 |           0 |
  25. +-----+---------+-----+-------------+

外连接到TAKE表是为了确保把那些没有任何选修课程的学生也能筛选出来。接着调用MAX函数之找出最大的CASE表达式返回值。如果一个学生选修了CS112课程，最大值会是1，因为其他课程对应的值都是0。对于GROUP BY解决方案而言，最后一步借助HAVING子句筛选出表达式返回值为0的学生。对于窗口函数而言，我们需要把上面的查询放入一个内嵌试图，并在外层查询中引用TAKE_CS112，因为WHERE子句不能引用窗口函数的查询结果。

原解决方案

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

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

B2.2 问题2

找出只选修了CS112和CS114中的一门，而不是两门都选的学习。下面查询语句看似有道理，但返回结果不对。

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

因为一个学生会选修多门课程，在这里我们应该为每个学生只返回一行记录，并创建一个字段用于标记该学生是否选了CS112或CS114，或者同时选了两门课程。这总做法使得我们能够很容易确认一个学生是否同时选修了这两门课程，并且不必多次扫描原表数据。

MySQL

使用CASE表达式和聚合函数SUM找出选修CS112或CS114。

  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') then 1  else 0 end) = 1

使用内联方式，可以排除没有选修任何课程的学生。

B.2.3 问题3

找出选修了CS112，而且没有选修其他课程的学习。(找出只选修了CS112的学生)

MySQL

  1. select s.*
  2.   from student s,
  3.        take t1,
  4.        (select sno from take group by sno having count(*) = 1) t2
  5.  where s.sno = t1.sno
  6.    and t1.sno = t2.sno
  7.    and t1.cno = 'CS112'

Oracle

使用窗口函数COUNT OVER确保下列查询返回的学生只选修了一门课程。

 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';

B.3 At Most条件问题

筛选满足某个条件的行，并限定其行的数目。

B.3.1 问题4

找出最多选修2门课的学生，没有任何选修课程的排查在外。

MySQL

 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 count(*) <= 2;

Oracle

窗口函数

select distinct sno, sname, age
  from (select s.sno,
               s.sname,
               s.age,
               count(*) over(partition by s.sno, s.sname, s.age) as cnt
          from student s, take t
         where s.sno = t.sno) x
 where cnt <= 2;

B.3.2 问题5

找出年龄最多大于其他两名的同学的学生。

MySQL

使用聚合COUNT和关联查询找出比其他0个 1个或2个学生年龄大的学生。

 select s1.*
   from student s1
  where 2 >= (select count(*) from student s2 where s2.age < s1.age)

Oracle

select sno, sname, age
from (select sno, sname, age, dense_rank() over(order by age) as dr
            from student) x
where dr <= 3 ;

B.4 At Least条件问题

一般来说，如果我们能从需求中识别出阀值，那么问题就算解决了一半。确立阀值之后，我们可以选择解决问题的方式：从正面入手(使用聚合函数或窗口函数)，或者从侧面入手(使用子查询和逻辑否定)

B.4.1 问题6 找出至少选修了两门课的学生。

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


select distinct sno, sname, age
  from (select s.sno,
               s.sname,
               s.age,
               count(*) over(partition by s.sno, s.sname, s.age) as cnt
          from student s, take t
         where s.sno = t.sno) x
 where cnt >= 2

B.4.2 问题7 找出同时选修CS112和CS114的学生，同时也仅选修了这两门课程

MySQL

使用MIN和MAX找出同时选修这两门课

select s.sno, s.sname, s.age
  from student s, take t
 where s.sno = t.sno
   and t.cno in ('CS114', 'CS112')
 group by s.sno, s.sname, s.age
having min(t.cno) != max(t.cno);

B4.3 问题8 找出至少比其他两位学生年龄大的学生

 select s1.*
   from student s1
  where 2 <= (select count(*) from student s2 where s2.age < s1.age)


select sno, sname, age
  from (select sno, sname, age, dense_rank() over(order by age) as dr
          from student) x
 where dr >= 3

B.5 Exactly问题

确定某些条件是否成立。

B.6 Any和All问题

B.6.1 问题12 找出选修了全部课程的学生

使用聚合函数COUNT找出选修所有课程的学生

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 count(t.cno) = (select count(*) from courses)