56,679
社区成员
发帖
与我相关
我的任务
分享
mysql> select * from t_JerryBeckF;
+------+-------+--------+-------+
| id | name | type | score |
+------+-------+--------+-------+
| 1 | ming | 中学生 | 50 |
| 2 | wang | 大学生 | 80 |
| 3 | lilei | 小学生 | 70 |
| 4 | qiang | 小学生 | 60 |
| 5 | mimi | 中学生 | 66 |
| 6 | coco | 大学生 | 55 |
| 7 | alex | 中学生 | 44 |
| 8 | bruce | 大学生 | 88 |
| 9 | tom | 小学生 | 91 |
+------+-------+--------+-------+
9 rows in set (0.00 sec)
mysql> select *
-> from t_JerryBeckF a
-> where 2>(select count(*)
-> from t_JerryBeckF
-> where type=a.type
-> and score>a.score)
-> order by type,score desc;
+------+-------+--------+-------+
| id | name | type | score |
+------+-------+--------+-------+
| 9 | tom | 小学生 | 91 |
| 3 | lilei | 小学生 | 70 |
| 5 | mimi | 中学生 | 66 |
| 1 | ming | 中学生 | 50 |
| 8 | bruce | 大学生 | 88 |
| 2 | wang | 大学生 | 80 |
+------+-------+--------+-------+
6 rows in set (0.00 sec)
mysql>
select *
from yourTable a
where 2>(select count(*) from yourTable where type=a.type and score>a.score)