MySQL 自定义变量@ 常用案例

小鱼儿 2023-07-13 15:12 225阅读 0赞

大家好，我是知数堂SQL 优化班老师 网名：骑龟的兔子

很久没有写文章，最近碰到了一个非常有意思的Oracle SQL 案例，

这个案例，我用了一些窗口函数来解决的，后来想想，能否跟MySQL

有所关联，就用一个SQL,总结了@的常用用法。

首先我们看下，如下案例

with w1 as (
         select 1    rn , 'cityhp01'        mber_id     from dual union all
         select 2   , 'uvis6921'     mber_id     from dual union all
         select 3   , 'uvis6921'     mber_id     from dual union all
         select 4   , 'moonhwa119'   mber_id    from dual union all
         select 5   , 'moonhwa119'   mber_id    from dual union all
         select 6   , 'b2500795'     mber_id     from dual union all
         select 7   , 'b2500795'     mber_id     from dual union all
         select 8   , 'b2500795'     mber_id     from dual union all
         select 9   , 'dumc1234'     mber_id     from dual union all
         select 10  , 'dumc1234'     mber_id     from dual
         )select * from w1;
    +----+------------+
    | rn | mber_id    |
    +----+------------+
    |  1 | cityhp01   |
    |  2 | uvis6921   |
    |  3 | uvis6921   |
    |  4 | moonhwa119 |
    |  5 | moonhwa119 |
    |  6 | b2500795   |
    |  7 | b2500795   |
    |  8 | b2500795   |
    |  9 | dumc1234   |
    | 10 | dumc1234   |
    +----+------------+

如上图所示，现有如下数据，在这个基础上希望得到如下结果

+----+------------+------+
    | RN | MBER_ID    | S1   |
    +----+------------+------+
    |  1 | cityhp01   |    1 |
    |  2 | uvis6921   |    2 |
    |  3 | uvis6921   |    2 |
    |  4 | moonhwa119 |    3 |
    |  5 | moonhwa119 |    3 |
    |  6 | b2500795   |    4 |
    |  7 | b2500795   |    4 |
    |  8 | b2500795   |    4 |
    |  9 | dumc1234   |    5 |
    | 10 | dumc1234   |    5 |
    +----+------------+------+

一般这种情况，我们第一个想到的是dense\_rank 函数

在下面的SQL 中w1 是上面的的with 语句里的w1 因为占用篇幅所以省略

-> SELECT
        -> RN ,MBER_ID
        ->  ,DENSE_RANK() OVER(ORDER BY  MBER_ID) R
        -> FROM W1
        -> ORDER BY RN;
    +----+------------+---+
    | RN | MBER_ID    | R |
    +----+------------+---+
    |  1 | cityhp01   | 2 |
    |  2 | uvis6921   | 5 |
    |  3 | uvis6921   | 5 |
    |  4 | moonhwa119 | 4 |
    |  5 | moonhwa119 | 4 |
    |  6 | b2500795   | 1 |
    |  7 | b2500795   | 1 |
    |  8 | b2500795   | 1 |
    |  9 | dumc1234   | 3 |
    | 10 | dumc1234   | 3 |
    +----+------------+---+

可以看到，单纯的使用dense\_rank 可以得到 相同编号，但是顺序是打乱了！

这个案例是Oracle SQL案例，如果是MySQL 就比较简单因为有@

select rn ,mber_id
        -> , @dense_rank:=@dense_rank+ case when @de=mber_id then 0 else 1 end drn
        ->  ,@de:=mber_id d1 from w1 ,(select   @dense_rank:=0 ,  @de:='' ) b
        -> ;

![format_png][]

但是Oracle 没有自定义变量@，但是有窗口函数

我们首先分析下，MySQL 自定义写法

第一  @dense\_rank:=@dense\_rank+ case when @de=mber\_id then 0 else 1 end drn

这段代码的意思是 如果 @de=mber\_id 那么就对 @dense\_rank + 0 否则 +1

而且累加

@de:=mber\_id 这个意思是把 mber\_id  赋值给 @de 而刚开始 @de为‘’

所以就是 @de=mber\_id 这段代码就是比较之前一行的值跟现在的比较

而Oracle 中 Lag 函数就可以求出之前一行的值

-> SELECT
        -> RN ,MBER_ID
        -> ,LAG(MBER_ID,1,'0' ) OVER(ORDER BY  RN) R
        -> FROM W1
        -> ORDER BY RN;
    +----+------------+------------+
    | RN | MBER_ID    | R          |
    +----+------------+------------+
    |  1 | cityhp01   | 0          |
    |  2 | uvis6921   | cityhp01   |
    |  3 | uvis6921   | uvis6921   |
    |  4 | moonhwa119 | uvis6921   |
    |  5 | moonhwa119 | moonhwa119 |
    |  6 | b2500795   | moonhwa119 |
    |  7 | b2500795   | b2500795   |
    |  8 | b2500795   | b2500795   |
    |  9 | dumc1234   | b2500795   |
    | 10 | dumc1234   | dumc1234   |
    +----+------------+------------+

然后比较mber\_id 和 r 如果相同则+0 否者 +1 而且累加

, W2 AS (
        -> SELECT
        -> RN ,MBER_ID
        -> ,LAG(MBER_ID,1,'0' ) OVER(ORDER BY  RN) R
        -> FROM W1
        -> ORDER BY RN
        -> )
        -> SELECT W2.*
        -> ,SUM(
        -> CASE WHEN MBER_ID=R THEN 0 ELSE 1 END
        -> ) OVER(ORDER BY RN ) S1
        ->  FROM W2 ;

![format_png 1][]

这样就可以得到我们想要的结果s1

最后给大家提供一个包含有row\_number,dense\_rank的 @变量用法

以便大家当参考

select rn ,mber_id, @rn:= @rn+1 rn1
    , case when @de = mber_id then @row_num := @row_num + 1
           else @row_num := 1 end nrn
    , @dense_rank:=@dense_rank+ case when @de=mber_id then 0 else 1 end drn
     ,@de:=mber_id d1 from w1 ,(select @rn:=0 , @row_num:=0 , @dense_rank:=0 ,  @de:='' ) b
    ;

![format_png 2][]

谢谢大家~ 欢迎转发

我是知数堂SQL 优化班老师~ ^^

如有关于SQL优化方面疑问和一起交流的请加 并且 @兔子@知数堂SQL优化

高性能MySQL,SQL优化群 有叶金荣，吴炳锡 两位大神坐镇 ：579036588

欢迎加入 知数堂大家庭。

我的微信公众号：SQL开发与优化(sqlturning)

郑老师《**SQL优化**》课程已经在腾讯课堂开播，下面是报名小程序码，恳请大家推荐给需要的小伙伴们。

![format_png 3][]

手机QQ扫码入群向助教咨询

![format_png 4][]

点“在看”给我一朵小黄花![format_png 5][]

![format_png 6][]

[format_png]: /images/20230529/e052873449044c8f860fc9f4119cba21.png
[format_png 1]: /images/20230529/86286147768346f0be8f178d79d9a5ce.png
[format_png 2]: /images/20230529/82a6e540b5b64696b0faa73c084258c0.png
[format_png 3]: /images/20230529/c890c6e4ade141e087e4e91be087fc89.png
[format_png 4]: /images/20230529/17cdad63782c41de89ebcb8ac22e4028.png
[format_png 5]: /images/20230529/ce7f025605be456d888a2121b261620b.png
[format_png 6]: /images/20230529/58f32965299d4ca18ec4b61927f25422.png