mysql 自定义数据_MySQL数据库自定义变量@的用法与常用案例

很久没有写文章，最近碰到了一个非常有意思的Oracle SQL 案例，这个案例，我用了一些窗口函数来解决的，后来想想，能否跟MySQL有所关联，就用一个SQL,总结了@的常用用法。

首先我们看下，如下案例

withw1as(

select1    rn ,'cityhp01'mber_idfromdualunionall

select2   ,'uvis6921'mber_idfromdualunionall

select3   ,'uvis6921'mber_idfromdualunionall

select4   ,'moonhwa119'mber_idfromdualunionall

select5   ,'moonhwa119'mber_idfromdualunionall

select6   ,'b2500795'mber_idfromdualunionall

select7   ,'b2500795'mber_idfromdualunionall

select8   ,'b2500795'mber_idfromdualunionall

select9   ,'dumc1234'mber_idfromdualunionall

select10  ,'dumc1234'mber_idfromdual

)select*fromw1;

+----+------------+

| 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 就比较简单因为有@

selectrn ,mber_id

-> , @dense_rank:[email protected]_rank+ casewhen@de=mber_idthen0else1enddrn

->  ,@de:=mber_id d1 fromw1 ,(select@dense_rank:=0 ,  @de:='') b

-> ;

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

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

第一  @dense_rank:[email protected]_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(ORDERBYRN) R

-> FROMW1

-> ORDERBYRN;

+----+------------+------------+

| 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 WHENMBER_ID=R THEN 0 ELSE 1 END

->) OVER(ORDER BY RN ) S1

->FROM W2 ;

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

最后给大家提供一个包含有row_number,dense_rank的 @变量用法

以便大家当参考

selectrn ,mber_id, @rn:= @rn+1 rn1

, casewhen@de = mber_idthen@row_num := @row_num + 1

else@row_num := 1endnrn

, @dense_rank:[email protected]_rank+ casewhen@de=mber_idthen0else1enddrn

,@de:=mber_id d1 fromw1 ,(select@rn:=0 , @row_num:=0 , @dense_rank:=0 ,  @de:='') b

;