我有以下 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