给mysql查询添加序号列

最新推荐文章于 2025-09-06 09:33:29 发布

weixin_30897233

最新推荐文章于 2025-09-06 09:33:29 发布

阅读量76

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/-scl/p/8484479.html

本文介绍了一种在MySQL中实现自增序号的方法，并通过一个多表联查的例子展示了如何在一个复杂查询中同时获取记录及其对应的自增序号。此方法适用于需要对查询结果进行排序或编号的情况。

select *,(@number := @number+1) AS number from
        (select u.mobile,u.`name`, ROUND((select  sum(r.realAmount) from t_recharge r where r.userid=u.id and  r.status='1' ),2)rechargeSum,           
        ROUND((select  sum(w.realAmount) from t_withdrawal w where w.userid=u.id and  w.status='1' ),2)withdrawalSum,          
        ROUND(sum(f.ownAmount),2)ownAmountSum,          
        ROUND(sum(f.platformFunds),2)platformFundsSum,          
        ROUND((select sum(fd.amount) from t_fund_detail fd where fd.userid=u.id and fd.type='3' ),2)feeSum,             
        ROUND(sum(f.result),2) cashResult ,
        u.balance,u2.`name` as froms               
         from t_user u
        left join t_financing f on f.userid=u.id
        LEFT JOIN t_user u2 ON u2.id = u.fromId
        group by u.id ) temp ,(select @number :=0) b