......

    记得以前有次被面试问到SQL,当时没答上来,当时也没鼓捣出来(当时对SQL也确实太不熟了(⊙o⊙))。

    今天突然想起来了,就重新拿出来,解决后在此处记录一下。

    切入正题(下面基于Oracle):

    表是这样一张表:

CREATE SEQUENCE seq_grade
  increment by 1  -- 每次递增1
  start with 1    -- 从1开始
  nomaxvalue      -- 没有最大值
  minvalue 1      -- 最小值=1
  NOCYCLE;        -- 不循环
--
create table grade(
id number primary key,
student nvarchar2(10),
course nvarchar2(10),
grade number
);
--
insert into grade values(seq_grade.nextval,'tom',    'math',64);
insert into grade values(seq_grade.nextval,'tom', 'english',34);
insert into grade values(seq_grade.nextval,'tom',     'cpp',67);
insert into grade values(seq_grade.nextval,'bob',    'math',23);
insert into grade values(seq_grade.nextval,'bob', 'english',89);
insert into grade values(seq_grade.nextval,'bob',     'cpp',45);
insert into grade values(seq_grade.nextval,'lily',   'math',78);
insert into grade values(seq_grade.nextval,'lily','english',89);
insert into grade values(seq_grade.nextval,'lily',    'cpp',34);
insert into grade values(seq_grade.nextval,'timo',   'math',56);
insert into grade values(seq_grade.nextval,'timo','english',79);
insert into grade values(seq_grade.nextval,'timo',    'cpp',34);
insert into grade values(seq_grade.nextval,'ez',     'math',78);
insert into grade values(seq_grade.nextval,'ez',  'english',56);
insert into grade values(seq_grade.nextval,'ez',      'cpp',98);

    解释:seq_grade是用于grade表的id自增长而创建的。

    看下数据:

tom math    64
tom english 34
tom cpp 67
bob math    23
bob english 89
bob cpp 45
lily    math    78
lily    english 89
lily    cpp 34
timo    math    56
timo    english 79
timo    cpp 34
ez  math    78
ez  english 56
ez  cpp 98

    问题是:得到每科成绩前3的人的所以信息。

    涉及到这种问题,首先就想到用科目来分组,可这就是一个很大的坑,因为 group by 如果不与聚合函数结合使用,是毫无意义的,甚至在有些数据库(比如Oracle)是会报错的。

    下面就直接给出正确的SQL:

select * from grade g
  where(
    select count(*) from grade where g.course=course and g.grade<grade
  ) < 3
order by course,grade desc;

    结果:

45  ez  cpp 98
33  tom cpp 67
36  bob cpp 45
35  bob english 89
38  lily    english 89
41  timo    english 79
37  lily    math    78
43  ez  math    78
31  tom math    64

    结果肯定是对的。

    如果使用java代码,我们可以先取出一个科目的列表,然后迭代取出他们的前三,最后组合。但是这样会造成多次访问数据库,效果并不理想。那么要把这个过程原子化也可以选择使用存储过程,但是总感觉有点滥用存储过程了。

    所以使用SQL依然是此处的最佳选择。

    上面的SQL,难点还是在where子句中的子查询:我们吧最外层的select看做一个循环,它遍历的是经过了排序后的行,然后条件中的子查询就是从遍历的时候,使用当前的值,与该表的相同科目下成绩的对比(相当于连表查询了),并且这样的结果的个数小于3的话,表示这个成绩是这个科目下中的前三的,所以这就是条件。

    当然这里没有考虑第三名与后面的成绩相同的情况。