【Mysql】Mysql似oracle分析函式sum over的實現

連續區間的實現（求連續id區間）

1. 第一步：標示
2. mysql> SELECT id,@id:=@id+1  AS alias1 FROM tbl,(SELECT @id:=0) AS id;
   +----+--------+
   | id | alias1 |
   +----+--------+
   | 11 |      1 |
   | 12 |      2 |
   | 13 |      3 |
   | 14 |      4 |
   | 15 |      5 |
   | 16 |      6 |
   | 18 |      7 |
   | 19 |      8 |
   +----+--------+
   8 rows in set (0.00 sec)
   
   

第二步：計算一下與標示的差值（如果是連續的，那麼差值一樣）
mysql> SELECT id,alias1,(id-alias1) AS diff FROM (SELECT id,@id:=@id+1  AS alias1 FROM tbl,(SELECT @id:=0) AS id) b;
+----+--------+------+
| id | alias1 | diff |
+----+--------+------+
| 11 |      1 |   10 |
| 12 |      2 |   10 |
| 13 |      3 |   10 |
| 14 |      4 |   10 |
| 15 |      5 |   10 |
| 16 |      6 |   10 |
| 18 |      7 |   11 |
| 19 |      8 |   11 |
+----+--------+------+
8 rows in set (0.00 sec)


第三步：根據差值分組找出最大最小即可
mysql> SELECT MIN(id) start_pos,MAX(id) end_pos
    -> FROM
    -> (SELECT id,alias1,(id-alias1) AS diff FROM (SELECT id,@id:=@id+1  AS alias1 FROM tbl,(SELECT @id:=0) AS id) b)
    -> AS c
    -> GROUP BY diff;
+-----------+---------+
| start_pos | end_pos |
+-----------+---------+
|        11 |      16 |
|        18 |      19 |
+-----------+---------+
2 rows in set (0.00 sec)



實驗：求tel相同的連續段

1. MySQL [interface_hd_com]> select * from testtab;
   
2. +------+--------+
   
3. | id | tel |
   
4. +------+--------+
   
5. | 1 | 187163 |
   
6. | 2 | 187163 |
   
7. | 3 | 187164 |
   
8. | 4 | 187164 |
   
9. | 5 | 187163 |
   
10. | 6 | 187163 |
    
11. | 7 | 187164 |
    
12. | 8 | 187163 |
    
13. | 9 | 19999 |
    
14. +------+--------+
    
15. 9 rows in set (0.00 sec)

按照上面的思路求得
MySQL [interface_hd_com]> SELECT MIN(id) start_pos,MAX(id) end_pos,tel FROM (SELECT id,alias1,(id-alias1) AS diff,tel FROM (SELECT id,@id:=@id+1  AS alias1,tel FROM testtab,(SELECT @id:=0) AS id) b) as c GROUP BY diff,tel order by tel desc;
+-----------+---------+--------+
| start_pos | end_pos | tel    |
+-----------+---------+--------+
|         3 |       7 | 187164 |
|         1 |       8 | 187163 |
|         9 |       9 |  19999 |
+-----------+---------+--------+   ---這樣是有bug的

發現這樣是不行的，因為id是連續的，所以同一個tel的diff是相同的，但其實中間隔著別的tel
解決辦法：分兩次求在合併

1. MySQL [interface_hd_com]> SELECT MIN(id) start_pos,MAX(id) end_pos,tel FROM (SELECT id,alias1,(id-alias1) AS diff,tel FROM (SELECT id,@id:=@id+1 AS alias1,tel FROM testtab,(SELECT @id:=0) AS id where tel in (SELECT distinct(tel) from testtab where tel<>187164)) b) as c GROUP BY diff,tel order by tel desc;
   
2. +-----------+---------+--------+
   
3. | start_pos | end_pos | tel |
   
4. +-----------+---------+--------+
   
5. | 1 | 2 | 187163 |
   
6. | 5 | 6 | 187163 |
   
7. | 8 | 8 | 187163 |
   
8. | 9 | 9 | 19999 |
   
9. +-----------+---------+--------+
   
10. 4 rows in set (0.00 sec)
    
11. 
    
12. MySQL [interface_hd_com]> SELECT MIN(id) start_pos,MAX(id) end_pos,tel FROM (SELECT id,alias1,(id-alias1) AS diff,tel FROM (SELECT id,@id:=@id+1 AS alias1,tel FROM testtab,(SELECT @id:=0) AS id where tel in (187164)) b) as c GROUP BY diff,tel order by tel desc;
    
13. +-----------+---------+--------+
    
14. | start_pos | end_pos | tel |
    
15. +-----------+---------+--------+
    
16. | 3 | 4 | 187164 |
    
17. | 7 | 7 | 187164 |
    
18. +-----------+---------+--------+
    
19. 2 rows in set (0.00 sec)

union 一下