MySQL 使用 ORDER BY 错误编号获取行位置 [英] MySQL get row position with ORDER BY incorrectly numbered

问题描述

我有以下 mySQL 查询，我想将在计算字段上排序的行与相应的行位置进行排序.我使用 here 中的 OMG Ponies 代码作为模板，但是行返回的编号不正确(它们按键(gemid)顺序编号，没有排序).我知道这与 GROUP BY 上的 JOIN 有关，但我知道如何解决它.提前致谢.

I have the following mySQL query which I want to give the rows sorted on a calculated field with the corresponding row position. I've used OMG Ponies code from here as a template but the rows are coming back incorrectly numbered (they are being numbered in key (gemid) order without the sort). I know it has to do with the JOIN on a GROUP BY but I know know how to fix it. Thanks in advance.

SELECT g.gemid, sum_rating, @rownum := @rownum + 1 AS row_num FROM gems g
    LEFT JOIN (SELECT gemid, SUM(rating) as sum_rating from gemrating GROUP BY gemid) rt ON g.gemid = rt.gemid 
    JOIN (SELECT @rownum := 0) Z 
    WHERE g.grade = '8'  
    ORDER BY sum_rating asc

输出应该看起来像:

gemid    sum_rating    row_num
------   ------------  ----------
2           10           1
4           25           2
1           40           3
3           41           4

相反，它又回来了:

gemid    sum_rating    row_num
------   ------------  ----------
2           10           2
4           25           4
1           40           1
3           41           3

推荐答案

看起来像这样.我知道我必须先对记录进行排序，然后再给它们编号，然后从有序列表中进行 SELECT.

Looks like this works. I knew I had to sort the records first before numbering them, and then SELECT from the ordered list.

SELECT g2.gemid, g2.sum_rating, @rownum := @rownum + 1 AS row_num FROM 

(SELECT g.gemid, rt.sum_rating, g.grade FROM gems g
    LEFT JOIN (SELECT gemid, SUM(rating) as sum_rating from gemrating GROUP BY gemid) rt ON g.gemid = rt.gemid 
 WHERE g.grade = '8'  ) g2

    JOIN (SELECT @rownum := 0) Z 
    WHERE g2.grade = '8'  
    ORDER BY sum_rating asc

这篇关于MySQL 使用 ORDER BY 错误编号获取行位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！