分组后取N条记录

作者在 2017-09-13 22:04:49 发布以下内容
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.班级
VFP中SQL查询问题 | 阅读 1478 次
文章评论,共0条
游客请输入验证码
浏览376979次