mysql 统计排名

发布网友发布时间：2022-04-22 10:26

共3个回答

懂视网时间：2022-05-01 12:15

* FROM table_score ORDER BY score DESC;

技术分享

获取某个学生成绩排名并计算该学生和上一名学生成绩差，是并列排名

SELECT *,
(SELECT count(DISTINCT score) FROM table_score AS b WHERE a.score<b.score)+1 AS rank, #获取排名，并列
(SELECT b.score FROM table_score AS b WHERE b.score>a.score ORDER BY b.score LIMIT 1)-a.score AS subtract #获取和上一名学生成绩的差 
FROM table_score AS a WHERE a.s_id = 13; #获取学生周三的成绩排名和与上一名的成绩差

技术分享

获取所有学生成绩排名-并列排名

SELECT *,
(SELECT count(DISTINCT score) FROM table_score AS b WHERE a.score<b.score)+1 AS rank #获取排名-并列
FROM table_score AS a ORDER BY rank; #获取学生成绩排名

技术分享

获取所有学生成绩排名，不是并列排名。计算行号进行排名

SELECT a.*,
(@rowNum:=@rowNum+1) AS rank #计算行号FROM table_score AS a,
(SELECT (@rowNum :=0) ) b
ORDER BY a.score DESC;

技术分享

mysql计算排名

标签：

热心网友时间：2022-05-01 09:23

SELECT * FROM table_name ORDER BY `score` DESC
就是从表中按score从高到低排序后取出的结果

热心网友时间：2022-05-01 10:41

set @mycnt = 0;
SELECT * FROM (
SELECT id,score,(@mycnt := @mycnt + 1) as rank FROM tab t ORDER BY score desc ) a ORDER BY id追问我想把这个结果查出来后再插入到另一张表中，这个能做到吗？还有java 用jdbc能调用你给的这个语句吗

追答insert into newtab

SELECT * FROM (
SELECT id,score,(@mycnt := @mycnt + 1) as rank FROM tab t ORDER BY score desc ) a ORDER BY id;
如果表结构不一样或字段顺序不一样，就要注明具体字段名；
在java中调用，我没试过，不过感觉封装到存储过程中调用的话，应该可以