mysql - MySql:如何将行号与联接查询语句一起使用？

我有一个MySQL语句，它连接两个表，并以字段“touch”的Desc顺序显示结果。这是我的SQL语句

SELECT @rownum := @rownum +1 rownum,
(IF( dm.Brand_FULL = '1', dd.Device_Brand, dm.Brand_Full )) AS Brand,
dd.Device_Model AS Model,
CONCAT( dm.Service_provider, ' ', dm.Model_Full ) AS 'Marketing Name',
max( Touch_Count ) AS Touch FROM Device_Details dd
JOIN Device_Models dm ON dd.Device_Model = dm.Model_Short,
(SELECT @rownum :=0 )r GROUP BY dd.Device_Model ORDER BY Touch_Count DESC

这给了我这样的结果
结果是perferct，它基于触摸字段的降序。现在的问题是rownum不按顺序排列。所以如何修改mySQL语句，以便在不更改Touch字段的DESC order的情况下按顺序获取rownum。

最佳答案

使用子查询：

SELECT
    @rownum := @rownum + 1 AS rownum,
    T1.*
FROM
(
    SELECT
        IF(dm.Brand_Full = '1', dd.Device_Brand, dm.Brand_Full) AS Brand,
        dd.Device_Model AS Model,
        CONCAT(dm.Service_provider, ' ', dm.Model_Full) AS 'Marketing Name',
        MAX(Touch_Count) AS Touch
    FROM Device_Details dd
    JOIN Device_Models dm
    ON dd.Device_Model = dm.Model_Short
    GROUP BY dd.Device_Model
) AS T1, (SELECT @rownum := 0) AS r
ORDER BY Touch DESC

关于mysql - MySql:如何将行号与联接查询语句一起使用？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/5057775/