MySQL实现查询结果带行号的方法

1. 前言

由于业务变动 PDF报告分析的人口学问题需要变动

为了不修改原代码结构的前提下 需要用SQL直接返回PO类 FinishCountPo

/**
 * Created by matioyoshitoki on 2019/6/10.
 */
public class FinishCountPo {
    private String tag;
    private String personCount ;
    private String basicType;
    private String displayNo;
    /* get set 方法省略 */ 
    private getXxx(); 
    private setXxx(X xxx); 
    ...
}

debug获得的数据里data 就是FinishCountPo 类

 SELECT tce.Gender as tag, COUNT(0) as personCount 
 FROM TCompanyEmployee tce 
 WHERE tce.GroupId = '2022031000002'
 GROUP BY tag

查询结果:

然后没有 displayNo 属性 突然想到可以用行号来解决 到时候配合 ORDER BY函数简直完美!

2. 解决

**使用mysql定义变量 查询结果 每行+1就可以解决 **

SELECT
	( @rowNum := @rowNum + 1 ) AS displayNo,
	tag,
	personCount 
FROM
	( 
		SELECT Gender AS tag, COUNT( 0 ) AS personCount 
		FROM TCompanyEmployee tce 
		WHERE GroupId = '2022031000002' 
		GROUP BY tag 
		ORDER BY personCount DESC 
	) AS temp,
	(
		SELECT ( @rowNum := 0 )
	) AS rowNum

查询结果:

问题解决啦!!