mybatis/mysql 返回序号根据条件统计出购买绿卡数量最多的前十名用户

SELECT @row_number := @row_number + 1 as rownum,dd.*  FROM (select @row_number := 0)AS ss,作为一张表来单独查询作为序号返回        rownum 就是返回的 序号名

count(1) AS fdGreenCardCount   返回参数绿卡个数       返回参数用户真实姓名：u.fd_user_real_name as fdUserRealName

u.fd_user_identity AS fdUserIdentity  返回参数用户身份证

 

通过手机号来进行两张表的关联字段

SELECT @row_number := @row_number + 1 as rownum,dd.*  FROM (select @row_number := 0)AS ss, 
(SELECT count(1) AS fdGreenCardCount,u.fd_user_real_name as fdUserRealName,u.fd_user_identity AS fdUserIdentity FROM 
tb_green_card g LEFT JOIN tb_marathon_userinfo u ON g.fd_user_phone = u.fd_user_phone
GROUP BY g.fd_user_phone ORDER BY fdGreenCardCount DESC LIMIT 10) dd