MySQL获取行位置,ORDER BY编号不正确



我有以下 mySQL 查询,我想给出在具有相应行位置的计算字段上排序的行。我从这里使用了 OMG Ponies 代码作为模板,但返回的行编号不正确(它们以键 (gemid) 顺序编号而没有排序)。我知道这与 GROUP BY 上的 JOIN 有关,但我知道如何解决它。提前谢谢。

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 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

相关内容

  • 没有找到相关文章

最新更新