如真要一条SQL,但有点长
SELECT a.* FROM (select *,RECNO() id FROM (SELECT * FROM cj ORDER BY 班别,成绩 desc) d1) a,(SELECT 班别,MIN(id) id FROM (select *,RECNO() id FROM (SELECT * FROM cj ORDER BY 班别,成绩 desc) d2) D3 GROUP BY 班别 )b WHERE a.班别==b.班别 AND a.id<=b.id+2
如果觉得长,也可以分成2条SQL
select *,RECNO() id FROM (SELECT * FROM cj ORDER BY 班别,成绩 desc) d1 INTO CURSOR cj_tmp
SELECT a.* FROM cj_tmp a,(SELECT 班别,MIN(id) id FROM cj_tmp GROUP BY 班别) bWHERE a.班别==b.班别 AND a.id<=b.id+2
如真要一条SQL,但有点长
SELECT a.* FROM (select *,RECNO() id FROM (SELECT * FROM cj ORDER BY 班别,成绩 desc) d1) a,(SELECT 班别,MIN(id) id FROM (select *,RECNO() id FROM (SELECT * FROM cj ORDER BY 班别,成绩 desc) d2) D3 GROUP BY 班别 )b WHERE a.班别==b.班别 AND a.id<=b.id+2
你觉得楼主的意思是取哪一种? 而我以下语句结果又是哪一种?
SELECT a.* FROM (select *,RECNO() id FROM (SELECT * FROM cj ORDER BY 班别,成绩 desc) d1) a,(SELECT 班别,MIN(id) id FROM (select *,RECNO() id FROM (SELECT * FROM cj ORDER BY 班别,成绩 desc) d2) D3 GROUP BY 班别 )b WHERE a.班别==b.班别 AND a.id<=b.id+2