1.建表语句,插入数据:
create table TEST_SCORE
(
CLASS VARCHAR(10),
NAME VARCHAR(10),
SCORE INTEGER
);
INSERT INTO test_score ( class, name, score) VALUES ( '1班', '齐静春', 100);
INSERT INTO test_score ( class, name, score) VALUES ( '1班', '李二', 99);
INSERT INTO test_score ( class, name, score) VALUES ( '1班', '陈平安', 88);
INSERT INTO test_score ( class, name, score) VALUES ( '1班', '李柳', 98);
INSERT INTO test_score ( class, name, score) VALUES ( '1班', '赵耀', 79);
INSERT INTO test_score ( class, name, score) VALUES ( '2班', '岳东江', 81);
INSERT INTO test_score ( class, name, score) VALUES ( '2班', '苏杰', 69);
INSERT INTO test_score ( class, name, score) VALUES ( '2班', '吴杰', 87);
INSERT INTO test_score ( class, name, score) VALUES ( '2班', '王珂', 77);
INSERT INTO test_score ( class, name, score) VALUES ( '2班', '杜兰特', 99);
INSERT INTO test_score ( class, name, score) VALUES ( '2班', '罗斯', 90);
INSERT INTO test_score ( class, name, score) VALUES ( '3班', '马祖号', 69);
INSERT INTO test_score ( class, name, score) VALUES ( '3班', '王祖辉', 89);
INSERT INTO test_score ( class, name, score) VALUES ( '3班', '林冲', 91);
INSERT INTO test_score ( class, name, score) VALUES ( '3班', '赵卡', 80);
INSERT INTO test_score ( class, name, score) VALUES ( '3班', '罗帅', 87);
INSERT INTO test_score ( class, name, score) VALUES ( '4班', '陈旭', 77);
INSERT INTO test_score ( class, name, score) VALUES ( '4班', '王艳', 71);
INSERT INTO test_score ( class, name, score) VALUES ( '4班', '呼呼', 89);
INSERT INTO test_score ( class, name, score) VALUES ( '4班', '陈帅', 78);
2.通用写法,不分数据库:
select class, name, score
from test_score a
where (select count(*) from test_score where class = a.class and a.score < score) < 3
order by a.class, a.score desc;
3.Oracle,DB2利用开口函数,
select class, name, score
from (select class, score, name,
rank() over (partition by class order by score desc ) as rownum
from test_score)
where rownum < 4;
4.MySQL利用变量实现,
set @pre_class:=null, @rownum:=0;
select
class, name, score
from
(SELECT class, score, name,
# @rownum:=if(@pre_class=class,@rownum+1,1) count,
@rownum:= (case when @pre_class = class then @rownum + 1 else 1 end) count,
@pre_class:=class
FROM test_score
order by class asc, score desc ) T
where count < 4;
5.查询结果如下,