分组后取N条记录

VFP中SQL查询问题 | 2017-09-13 22:04:49 | 112次阅读 | 0评
CREATE CURSOR  tt (班级 c(10),学号 c(10),成绩 n(3))
INSERT INTO tt VALUES ("01","X0101",86)
INSERT INTO tt VALUES ("01","X0102",92)
INSERT INTO tt VALUES ("01","X0103",72)
INSERT INTO tt VALUES ("01","X0104",86)
INSERT INTO tt VALUES ("02","X0201",75)
INSERT INTO tt VALUES ("02","X0202",81)
INSERT INTO tt VALUES ("02","X0203",72)
INSERT INTO tt VALUES ("02","X0204",73)
INSERT INTO tt VALUES ("03","X0204",74)
* 分组前N名(大)
SELECT A.班级,A.学号,A.成绩 FROM tt A WHERE 2>(SELECT COUNT(*) FROM tt WHERE 班级=A.班级 AND 成绩>A.成绩) ORDER BY a.班级,成绩 DESC 

* 分组前N名(小)
SELECT A.班级,A.学号,A.成绩 FROM tt A WHERE 2>(SELECT COUNT(*) FROM tt WHERE 班级=A.班级 AND 成绩<A.成绩) ORDER BY a.班级,成绩 DESC 

* 分组最大值所在行
select a.* from tt a where 成绩 = (select max(成绩) from tt where 班级 = a.班级) order by a.班级
* 分组最小值所在行
select a.* from tt a where 成绩 = (select min(成绩) from tt where 班级 = a.班级) order by a.班级
博友评论,共0条
浏览153579次