分组后取N条记录

VFP中SQL查询问题 | 2017-09-13 22:04:49 | 阅读 778 次 | 评论(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条
游客请输入验证码
浏览234099次
最新评论
  • cstdio:ooooooo
  • sdta:从VFP入手也不错
  • yuanhouwen:尊敬的sdta大神:作为一个编程外行,看到你关于操控EXCEL的编程程序我觉得特别神奇,想向你...