SQL 技巧构造分组依据

题目

数据 
0 
0 
0 
1 
2 
3 
4 
4 
4 
5 
6 
7 
8 
需求，000为一组，123为一组，444为一组，5678为一组，请用SQL实现

创建表

 
 
create table pig(id number);
insert into pig values(0);
insert into pig values(0);
insert into pig values(0);
insert into pig values(1);
insert into pig values(2);
insert into pig values(3);
insert into pig values(4);
insert into pig values(4);
insert into pig values(4);
insert into pig values(5);
insert into pig values(6);
insert into pig values(7);
insert into pig values(8);

Oracle

思路是构造分组条件列，为最后的 listagg 创造分组依据 
这里用 lag 折腾来折腾去，不太明白为什么，可能只是在找规律

 
 
with t1 as 
(select id,lag(id) over(order by id) lag_id from pig),
t2 as 
(select id, lag_id,case when id=lag_id then 0 else 1 end as flag from t1),
t3 as (
select id,lag_id,flag,lag(flag) over(order by id) lag2,lead(flag) over(order by id) lag3 from t2),
t4 as (
select id,lag_id,flag,lag2,lag3,case when lag2=1 and nvl(lag3,1)=1 then 0 else flag end as flag2 from t3),
t5 as (
select id,flag2,sum(flag2) over(order by id) gp from t4)
select listagg(id,',') within group(order by id) from t5 group by gp

最后两个 lag lead 在操作 flag 列，如果都等于1就让他等于0，否则等于 flag 
最后的 flag2列用 sum() over()就可以构造出分组依据了 

最后用 listagg 就可以了

Mysql

这里完全是模仿 Oracle 的思路

 
 
select GROUP_CONCAT(id order by id) from (
select id,flag2,@sum:=@sum+flag2 sum  from (
select id,flag,lag_flag,lead,case when lag_flag=1 and ifnull(lead,1)=1 then 0 else flag end flag2,rownum from (
select id,flag,lead,@lag lag_flag ,rownum,@lag:=flag from (
select id,lag,flag,@flag lead,@flag:=flag,rownum from (
select id,lag,case when id=lag then 0 else 1 end flag,rownum from (
select id,@id lag,@rn rownum,@rn:=@rn+1 ,@id:=id from pig,
(select @id:=null ,@rn:=1) b) a) a,(select @flag:=null) b order by rownum desc) a ,(select @lag:=null) b order by rownum) a)
a ,(select @sum:=0) b ) a group by sum

lag

 
 
select id,@id lag,@id:=id from pig,(select @id:=null) b ;
+------+------+---------+
| id   | lag  | @id:=id |
+------+------+---------+
|    0 | NULL |       0 |
|    0 | 0    |       0 |
|    0 | 0    |       0 |
|    1 | 0    |       1 |
|    2 | 1    |       2 |
|    3 | 2    |       3 |
|    4 | 3    |       4 |
|    4 | 4    |       4 |
|    4 | 4    |       4 |
|    5 | 4    |       5 |
|    6 | 5    |       6 |
|    7 | 6    |       7 |
|    8 | 7    |       8 |
+------+------+---------+
13 rows in set (0.00 sec)

在这一行 sql 中，首先声明变量@id:=null，之后 select 出 id 和@id ( 此时 id 的初始值是 null )，然后赋值@id:=id，此时@id 等于 第一行的0 
在第二行是，id=0,@id=之前赋值的0，然后再次赋值@id 等于第二行的0 
加上 rownum，位置后 lead 做准备

 
 
select id,@id lag,@rn rownum,@rn:=@rn+1 ,@id:=id from pig,(select @id:=null ,@rn:=1) b

lead

lead 的具体思路是 
1.生成 rownum 
2.根据 rownum 倒序 
3.按照 lag 方式实现 
4.再按照 rownum 正序

 
 
select id,lag,flag,@flag lead,@flag:=flag,rownum from (
select id,lag,case when id=lag then 0 else 1 end flag,rownum from (
select id,@id lag,@rn rownum,@rn:=@rn+1 ,@id:=id from pig,
(select @id:=null ,@rn:=1) b) a) a,(select @flag:=null) b order by rownum desc
+------+------+------+------+-------------+--------+
| id   | lag  | flag | lead | @flag:=flag | rownum |
+------+------+------+------+-------------+--------+
|    8 |    7 |    1 | NULL |           1 |     13 |
|    7 |    6 |    1 | 1    |           1 |     12 |
|    6 |    5 |    1 | 1    |           1 |     11 |
|    5 |    4 |    1 | 1    |           1 |     10 |
|    4 |    4 |    0 | 1    |           0 |      9 |
|    4 |    4 |    0 | 0    |           0 |      8 |
|    4 |    3 |    1 | 0    |           1 |      7 |
|    3 |    2 |    1 | 1    |           1 |      6 |
|    2 |    1 |    1 | 1    |           1 |      5 |
|    1 |    0 |    1 | 1    |           1 |      4 |
|    0 |    0 |    0 | 1    |           0 |      3 |
|    0 |    0 |    0 | 0    |           0 |      2 |
|    0 | NULL |    1 | 0    |           1 |      1 |
+------+------+------+------+-------------+--------+

最后在按照 rownum 正序

sum() over()

没有 partition by 的情况就是

 
 
@sum:=@sum+flag2 
(select @sum:=0) b 

partition by

 
 
SELECT empno,  
       ename,  
       sal,  
       IF(@deptno = deptno, @sal := @sal, @sal := s_sal) AS s_sal,  
       @deptno := deptno  
FROM  
(  
SELECT empno,  
       ename,  
       sal,  
       IF(@deptno = deptno, @sal := @sal + sal, @sal := sal) AS s_sal,  
       @deptno := deptno AS deptno  
  FROM (SELECT @sal := 0, @deptno := 0) a0, emp t  
 ORDER BY deptno, empno  
)e  
 ORDER BY deptno, empno DESC;  
+-------+--------+------+-------+-------------------+  
| empno | ename  | sal  | s_sal | @deptno := deptno |  
+-------+--------+------+-------+-------------------+  
|  7934 | MILLER | 1300 |  8750 |                10 |  
|  7839 | KING   | 5000 |  8750 |                10 |  
|  7782 | CLARK  | 2450 |  8750 |                10 |  
|  7902 | FORD   | 3000 | 10875 |                20 |  
|  7876 | ADAMS  | 1100 | 10875 |                20 |  
|  7788 | SCOTT  | 3000 | 10875 |                20 |  
|  7566 | JONES  | 2975 | 10875 |                20 |  
|  7369 | SMITH  |  800 | 10875 |                20 |  
|  7900 | JAMES  |  950 |  9400 |                30 |  
|  7844 | TURNER | 1500 |  9400 |                30 |  
|  7698 | BLAKE  | 2850 |  9400 |                30 |  
|  7654 | MARTIN | 1250 |  9400 |                30 |  
|  7521 | WARD   | 1250 |  9400 |                30 |  
|  7499 | ALLEN  | 1600 |  9400 |                30 |  
+-------+--------+------+-------+-------------------+  
14 rows in set (0.01 sec)