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;