大家好，我是知数堂SQL 优化班老师 网名：骑龟的兔子
很久没有写文章，最近碰到了一个非常有意思的Oracle SQL 案例，
这个案例，我用了一些窗口函数来解决的，后来想想，能否跟MySQL
有所关联，就用一个SQL,总结了@的常用用法。
首先我们看下，如下案例
with w1 as (select 1 rn , 'cityhp01' mber_id from dual union allselect 2 , 'uvis6921' mber_id from dual union allselect 3 , 'uvis6921' mber_id from dual union allselect 4 , 'moonhwa119' mber_id from dual union allselect 5 , 'moonhwa119' mber_id from dual union allselect 6 , 'b2500795' mber_id from dual union allselect 7 , 'b2500795' mber_id from dual union allselect 8 , 'b2500795' mber_id from dual union allselect 9 , 'dumc1234' mber_id from dual union allselect 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 + 1else @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优化方面疑问和一起交流的请加 并且 @兔子@知数堂SQL优化
高性能MySQL,SQL优化群 有叶金荣，吴炳锡 两位大神坐镇 ：579036588
欢迎加入 知数堂大家庭。
