mysql实现row_number()和row_number() over(partition by)

row_number() 仅仅是价格排序字段

select @rownum := @rownum + 1 as rn, t.*
  from (select @rownum := 0) r, test_table t


select 
@rank:=@rank+1 as rank
,t.*
from (select @rank:=0) r, DM_GOA.dm_goa_fin_inv_mly_d  t

;

解释：给test_table里的数据设置行号, rn是行号

 

row_number() over(partition by)

select if(@uid = t.user_id and @cid = t.city_id,
          @rank := @rank + 1,
          @rank := 1) as rank,
       t.*,
       @uid := t.user_id,
       @cid := t.city_id
  from (select @uid := null, @cid := null, @rank := 0) r,
       (select user_id, city_id, city_name, id
          from t_user_city
         order by user_id, city_id, id) t;

解释：对t_user_city表按照user_id,city_id两个字段做分组设置行号,rank是分组的行号,order by里一定要有两个分组字段及第三个排序字,上面if 也可以用case when

SELECT *
  FROM (SELECT
         F.PARTY_ID AS PARTY_ID,
         F.ALL_COUNT AS ALL_COUNT,
         F.SU AS SU,
         CASE
            WHEN @PARTY_ID != PARTY_ID THEN
            @ROWNUM := 1
           ELSE
            @ROWNUM := @ROWNUM + 1
         END AS ROWNUM,
         @PARTY_ID := PARTY_ID AS PARTY_ID12
          FROM (SELECT @F.PARTY_ID := “”) c,
               (SELECT @ROWNUM := 0) r,
               (SELECT
                 E.PARTY_ID AS PARTY_ID,
                  E.SU AS SU,
                  E.ALL_COUNT AS ALL_COUNT
                  FROM (SELECT
                          c.PARTY_ID AS PARTY_ID,
                           COUNT(*) AS SU, 
                           d.AMT AS ALL_COUNT   
                           FROM dpf_ckaccno_dtl d      
                           JOIN dpf_ckaccno c
                             ON d.ACC_NO = c.DM01ACCT
                          WHERE  d.CRDFLG = ‘D’
                          GROUP BY c.PARTY_ID, 
                                    d.AMT
                         ) E
                 ORDER BY E.PARTY_ID,
                           E.SU DESC,
                           E.ALL_COUNT DESC
                ) F) G
 WHERE G.ROWNUM <= 3

说明：1、(SELECT @ROWNUM := 0)r 用于定义变量并初始化，后面是命别名，此处不能省略，不然会报错。

2、 @PARTY_ID := PARTY_ID AS PARTY_ID12 此处是把当前行值赋值给PARTY_ID 变量，前面(SELECT @F.PARTY_ID := “”) c，已经将其初始化为空串

3、CASE

           WHEN @PARTY_ID != PARTY_ID THEN

                     @ROWNUM := 1

                   ELSE

                  @ROWNUM :=@ROWNUM + 1

                END AS ROWNUM, – 此处通过case when 条件句来判断变量值与当前PARTY_ID是否相等来，不等说明是新的PARTY_ID，ROWNUM 就为1，否则就+1

4、通过这个sql，我们可以发现mysql在sql语句中使用变量时，初始化的东西放到from语句的后面，使变量的变化语句要放到查询结果里面，这里的原因可能是跟数据库管理系统解析一个sql的顺序有关系

 

             F.PARTY_ID AS PARTY_ID,
         F.ALL_COUNT AS ALL_COUNT,
         F.SU AS SU,      

PARTY_ID        ALL_COUNT   SU  ROW_NUM   PARTY_ID_12

102902                 5000            18           1                      102902

102902                 2000            18           2                      102902

102902                 2                   18          3                      102902

104631                 4900             12          1                      104631

104631                 500               12           2                     104631

104631                 2000              6            3                     104631

104658                 5000            192            1                     104658

104658                 2000            126            2                     104658

104658                 1000              48            3                     104658