Mysql 获取排列序号。

来源：互联网发布：彩票11选5遗漏数据分析编辑：程序博客网时间：2024/05/16 10:14

1、根据某字段降序排列获取排序号。

例如：

SELECT a.*,@rank:=@rank+1 as rownum FROM (
SELECT t1.* FROM (
SELECT USER_ID,SUM(ACCOUNT) first_tender_total FROM t_first_tender_real WHERE `STATUS` = 0 GROUP BY USER_ID
) t1 ORDER BY t1.first_tender_total desc
) a,(SELECT @rank:=0) b

说明：根据first_tender_total字段降序排名，生成排序号。

2、根据某字段降序排列获取排序号，且当该字段的值存在多个相同的记录时，则相同值的记录的排序号相同。（即相同值排序号相同）

例如：

SELECT n.USER_ID,n.first_tender_total,m.rownum FROM (
SELECT a.*,@rank:=@rank+1 as rownum FROM (
SELECT * FROM (
SELECT USER_ID,SUM(ACCOUNT) first_tender_total FROM t_first_tender_real WHERE `STATUS` = 0 GROUP BY USER_ID
) t1 GROUP BY t1.first_tender_total ORDER BY t1.first_tender_total desc
) a,(SELECT @rank:=0) b
) m,
(
SELECT USER_ID,SUM(ACCOUNT) first_tender_total FROM t_first_tender_real WHERE `STATUS` = 0 GROUP BY USER_ID
) n WHERE n.first_tender_total = m.first_tender_total ORDER BY n.first_tender_total desc;

3、根据某字段降序排列获取排序号，且当该字段的值存在多个相同的记录时，则相同值的记录的排序号相同，并且下一组值的排序号为上一组内记录数+上一个排序号。

例如：

SELECT n.USER_ID,n.first_tender_total,m.rownum FROM (
SELECT a.*,@rank:=@rank+@sart as rownum,@sart:=a.num sart FROM (
SELECT t1.*,count(*) num FROM (
SELECT USER_ID,SUM(ACCOUNT) first_tender_total FROM t_first_tender_real WHERE `STATUS` = 0 GROUP BY USER_ID
) t1 GROUP BY t1.first_tender_total ORDER BY t1.first_tender_total desc
) a,(SELECT @rank:=1) b,(SELECT @sart:=0) c
) m,
(
SELECT USER_ID,SUM(ACCOUNT) first_tender_total FROM t_first_tender_real WHERE `STATUS` = 0 GROUP BY USER_ID
) n WHERE n.first_tender_total = m.first_tender_total ORDER BY n.first_tender_total desc;

0 0