以下文章来源于SQL开发与优化
大家好，我是知数堂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
-> ;
但是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 ;
这样就可以得到我们想要的结果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
;
谢谢大家~ 欢迎转发
我是知数堂SQL 优化班老师~ ^^
我的微信公众号：SQL开发与优化(sqlturning)
有问题请联系
