mysql中给查询的结果添加序号列

• 第一种，利用 SELECT 临时表

SELECT
        T2.num
        FROM
        (
        SELECT
        ( @i := @i + 1 ) AS num,
        T.groupName
        FROM
        ( SELECT groupName, SUM( all_amt ) allAmt FROM app_agent WHERE `year_month` >= #{beginDateTime} AND `year_month` <= #{endDateTime} GROUP BY groupName ORDER BY allAmt DESC ) T,(
        SELECT
        @i := 0
        ) AS N
        ) T2
        WHERE
        T2.groupName = #{groupName}

• 第二种，临时变量

SET @i = 0;
SELECT
	( @i := @i + 1 ) AS num,
	T.* 
FROM
	( SELECT org_name, SUM( all_amt ) allAmt FROM app_agent WHERE `year_month` >= 202001 AND `year_month` <= 202012 GROUP BY org_name ORDER BY allAmt DESC ) T

• 第三种，临时变量2

SELECT @rownum:=@rownum+1 AS rownum, XXXTable.* FROM (SELECT @rownum:=0) r, XXXTable

参考链接