......一个看似简单,实则容易踩坑的例子
记得以前有次被面试问到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自增长而创建的。
看下数据:
问题是:得到每科成绩前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;
结果:
结果肯定是对的。
如果使用java代码,我们可以先取出一个科目的列表,然后迭代取出他们的前三,最后组合。但是这样会造成多次访问数据库,效果并不理想。那么要把这个过程原子化也可以选择使用存储过程,但是总感觉有点滥用存储过程了。
所以使用SQL依然是此处的最佳选择。
上面的SQL,难点还是在where子句中的子查询:我们吧最外层的select看做一个循环,它遍历的是经过了排序后的行,然后条件中的子查询就是从遍历的时候,使用当前的值,与该表的相同科目下成绩的对比(相当于连表查询了),并且这样的结果的个数小于3的话,表示这个成绩是这个科目下中的前三的,所以这就是条件。
当然这里没有考虑第三名与后面的成绩相同的情况。