怎么查询指定条件的排名?
比如说:有一个表table,表里有3个字段:
Id:自增
Name:学生名
Score:得分 比如有学生 A,B,C,D,E
得分:90,90,80,80,70。得分相同的算一个名次。比如我查C的排名和得分
检索到的结果应该是:2,80
查E的排名,结果应该是:3,70一个语句能实现吗? 谢谢!
比如说:有一个表table,表里有3个字段:
Id:自增
Name:学生名
Score:得分 比如有学生 A,B,C,D,E
得分:90,90,80,80,70。得分相同的算一个名次。比如我查C的排名和得分
检索到的结果应该是:2,80
查E的排名,结果应该是:3,70一个语句能实现吗? 谢谢!
tb_score c,
(
select score,(select count(*) from (select score from tb_score group by score) b where b.score>=a.score) as Order_num
from (select score from tb_score group by score) a
) d
where c.score=d.score
+------+-------+
| name | score |
+------+-------+
| a | 90 |
| b | 90 |
| c | 80 |
| d | 80 |
| e | 70 |
+------+-------+
5 rows in set (0.00 sec)mysql> select c.*,d.Order_num from
-> tb_score c,
-> (
-> select score,(select count(*) from (select score from tb_score group by
score) b where b.score>=a.score) as Order_num
-> from (select score from tb_score group by score) a
-> ) d
-> where c.score=d.score;
+------+-------+-----------+
| name | score | Order_num |
+------+-------+-----------+
| a | 90 | 1 |
| b | 90 | 1 |
| c | 80 | 2 |
| d | 80 | 2 |
| e | 70 | 3 |
+------+-------+-----------+
5 rows in set (0.00 sec)mysql>
-> tb_score c,
-> (
-> select score,(select count(*) from (select score from tb_score group by
score) b where b.score>=a.score) as Order_num
-> from (select score from tb_score group by score) a
-> ) d
-> where c.score=d.score and c.name='B';
+------+-------+-----------+
| name | score | Order_num |
+------+-------+-----------+
| b | 90 | 1 |
+------+-------+-----------+
1 row in set (0.00 sec)mysql>
core) as 名次 from tb_score a where a.name='C';
+------+-------+------+
| name | score | 名次 |
+------+-------+------+
| c | 80 | 2 |
+------+-------+------+
1 row in set (0.02 sec)mysql>
如果不可以同名。那自增ID没什么用了吧。
+----+------+-------+
| id | name | score |
+----+------+-------+
| 1 | a | 90 |
| 2 | b | 90 |
| 3 | c | 80 |
| 4 | d | 80 |
| 5 | e | 70 |
+----+------+-------+
5 rows in set (0.08 sec)mysql> select *,
-> (select count(distinct score)
-> from t_xueseyanling
-> where score>t.score)+1 as sno
-> from t_xueseyanling t;
+----+------+-------+------+
| id | name | score | sno |
+----+------+-------+------+
| 1 | a | 90 | 1 |
| 2 | b | 90 | 1 |
| 3 | c | 80 | 2 |
| 4 | d | 80 | 2 |
| 5 | e | 70 | 3 |
+----+------+-------+------+
5 rows in set (0.00 sec)mysql>