MySql的聚合后排序

表一主表：

表二：关联表

 

统计表一的用户对应设备的数量排名，聚合后

SELECT @i:=@i+1 AS number,`name`,num
FROM
(
SELECT sur.`name`,count(*) AS num
FROM business_device_info bdi
INNER JOIN security_user sur ON bdi.user_id=sur.id
INNER JOIN security_role sr ON sur.role_id = sr.id
WHERE sur.valid=1 and sr.identity=1002
GROUP BY 1
ORDER BY 2 DESC
)
AS a,(SELECT @i:=0) AS b