问题:有表 如下,要求取出各班前两名(允许并列第二)
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 *
from Table1 a
where 2>(select count(*) from Table1 where ClsNo=a.ClsNo and Score>a.Score)
order by a.ClsNo,a.Score desc这条SQL,没看懂是什么意思,谁可以帮忙解答一下?谢谢。
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 *
from Table1 a
where 2>(select count(*) from Table1 where ClsNo=a.ClsNo and Score>a.Score)
order by a.ClsNo,a.Score desc这条SQL,没看懂是什么意思,谁可以帮忙解答一下?谢谢。
group by a.id
看看结果
from Table1 a
where 2>(select count(*) from Table1 where ClsNo=a.ClsNo and Score>a.Score)
order by a.ClsNo,a.Score desc
取出成绩前两名,然后按照学号、成绩降序排序。