从PHP&中的总和打印等级MySQL以1,1,3,4等顺序 [英] Print rank from sum in PHP & MySQL in 1,1,3,4, etc Order

问题描述

我有来自Stack Overflow的以下SQL查询，它运行正常. 这是原始答案的链接. /p>

如果有任何平局，我希望下面的查询按以下顺序输出排名:

1,1,3,4,5,6,6,6,9,10 ...等...

这是SQL查询:

SELECT idno, name, rank,total_score
FROM (SELECT *,  IF(@marks=(@marks:=total_score), @auto, @auto:=@auto+1) AS    rank 
FROM (SELECT * FROM 
 (SELECT idno, name, SUM(score) AS total_score 
FROM jbit, 
(SELECT @auto:=0, @marks:=0) as init 
 GROUP BY name) sub ORDER BY total_score DESC)t) as result
WHERE idno ='1'

解决方案

MySQL甚至可以使用标准的ORDER BY按值对值进行排序.它将按照您提到的顺序返回结果行； 1, 1, 3, 4, 5, 6, 6, 6, 9, 10.

SELECT idno, name, rank, total_score FROM table_name ORDER BY rank;

I have the below SQL query from Stack Overflow, and it's working perfectly. This is the link to the original answer.

I want the below query to output the rank in the following order if there is any tie:

1,1,3,4,5,6,6,6,9,10.... etc.

This is the SQL query:

SELECT idno, name, rank,total_score
FROM (SELECT *,  IF(@marks=(@marks:=total_score), @auto, @auto:=@auto+1) AS    rank 
FROM (SELECT * FROM 
 (SELECT idno, name, SUM(score) AS total_score 
FROM jbit, 
(SELECT @auto:=0, @marks:=0) as init 
 GROUP BY name) sub ORDER BY total_score DESC)t) as result
WHERE idno ='1'

解决方案

MySQL can sort by values even with ties using the standard ORDER BY. It'll return result rows in order as you mentioned; 1, 1, 3, 4, 5, 6, 6, 6, 9, 10.

SELECT idno, name, rank, total_score FROM table_name ORDER BY rank;

这篇关于从PHP&中的总和打印等级MySQL以1,1,3,4等顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！