mysql - 在 MySQL 查询中创建排名列

Question

我有一个 MySQL 查询，我想在其中添加一列以根据用户的余额对用户进行排名。查询是:

SELECT (display_name) 'Author',
IFNULL(ROUND(SUM(balance.meta_value),2),2) 'Balance',
IFNULL(ROUND((((SUM(balance.meta_value+bet.meta_value)-                                                         
SUM(bet.meta_value))/SUM(bet.meta_value))*100),2),2) 'Yield %'
FROM wp_posts p
 JOIN wp_users u 
ON p.post_author = u.ID
 LEFT JOIN wp_postmeta bet 
ON p.ID = bet.post_id AND bet.meta_key = 'bet'
LEFT JOIN wp_postmeta balance 
ON p.ID = balance.post_id AND balance.meta_key = 'balance'
WHERE p.post_status = 'publish'
GROUP BY u.ID
ORDER BY Balance DESC

我尝试添加 SET @rownum := 0;和@rownum := @rownum + 1 AS rank 但它不起作用，排名不按最高余额排序。

谁能帮帮我。谢谢。

Answer 1

select *, @rownum := @rownum + 1 from
(
SELECT (display_name) 'Author',
    IFNULL(ROUND(SUM(balance.meta_value),2),2) 'Balance',
    IFNULL(ROUND((((SUM(balance.meta_value+bet.meta_value)-SUM(bet.meta_value))/SUM(bet.meta_value))*100),2),2) 'Yield %'
FROM wp_posts p
JOIN wp_users u 
    ON p.post_author = u.ID
LEFT JOIN wp_postmeta bet 
    ON p.ID = bet.post_id AND bet.meta_key = 'bet'
LEFT JOIN wp_postmeta balance 
    ON p.ID = balance.post_id AND balance.meta_key = 'balance'
WHERE p.post_status = 'publish'
GROUP BY u.ID
ORDER BY Balance DESC
)x, (select @rownum := 0) r