本文共 1114 字，大约阅读时间需要 3 分钟。
问题：有表 如下，要求取出各班前两名（允许并列第二） Table1+----+------+------+-----+ | id |SName |ClsNo |Score| +----+------+------+-----+ | 1 |AAAA | C1 | 67 | | 2 |BBBB | C1 | 55 | | 3 |CCCC | C1 | 67 | | 4 |DDDD | C1 | 65 | | 5 |EEEE | C1 | 95 | | 6 |FFFF | C2 | 57 | | 7 |GGGG | C2 | 87 | | 8 |HHHH | C2 | 74 | | 9 |IIII | C2 | 52 | | 10 |JJJJ | C2 | 81 | | 11 |KKKK | C2 | 67 | | 12 |LLLL | C2 | 66 | | 13 |MMMM | C2 | 63 | | 14 |NNNN | C3 | 99 | | 15 |OOOO | C3 | 50 | | 16 |PPPP | C3 | 59 | | 17 |QQQQ | C3 | 66 | | 18 |RRRR | C3 | 76 | | 19 |SSSS | C3 | 50 | | 20 |TTTT | C3 | 50 | | 21 |UUUU | C3 | 64 | | 22 |VVVV | C3 | 74 | +----+------+------+-----+ 结果如下+----+------+------+-----+ | id |SName |ClsNo |Score| +----+------+------+-----+ | 5 |EEEE | C1 | 95 | | 1 |AAAA | C1 | 67 | | 3 |CCCC | C1 | 67 | | 7 |GGGG | C2 | 87 | | 10 |JJJJ | C2 | 81 | | 14 |NNNN | C3 | 99 | | 18 |RRRR | C3 | 76 | +----+------+------+-----+
方法一：
select a.id,a.SName,a.ClsNo,a.Scorefrom Table1 a left join Table1 b on a.ClsNo=b.ClsNo and a.Score<b.Scoregroup by a.id,a.SName,a.ClsNo,a.Scorehaving count(b.id)<2order by a.ClsNo,a.Score desc转自：
转载地址：http://vjljl.baihongyu.com/