一般用SP来完成 select name,start,interval,value from tth1 union all select a.name,if(b.start<>a.start+a.interval,a.start+a.interval,b.start) as newstart, if(b.start<>a.start+a.interval,b.start-newstart,a.start) as newinterval,a.value from tth1 a left join tth1 b on a.id=b.id-1 and b.start<>a.start+a.interval where b.id is not null order by start
可以将上述代码做成VIEW,再生成ID
mysql> select * from t_aimmaker; +------+------+-------+----------+-------+ | id | name | start | interval | value | +------+------+-------+----------+-------+ | 0 | xxx | 0 | 3 | 5 | | 1 | xxx | 3 | 2 | 2 | | 2 | xxx | 8 | 2 | 3 | +------+------+-------+----------+-------+ 3 rows in set (0.17 sec)mysql> select ( -> select count(*) from -> ( -> select name,start,`interval`,`value` from t_aimmaker -> union -> select a.name,a.start+a.`interval`,min(b.start)-a.start-a.`interval`,a.`value` -> from t_aimmaker a,t_aimmaker b -> where a.start<b.start -> group by a.id,a.start,a.`interval` -> having min(b.start)!=a.start+a.`interval` -> ) x where start<=t.start -> ) as id,name,start,`interval`,`value` -> from ( -> select name,start,`interval`,`value` from t_aimmaker -> union -> select a.name,a.start+a.`interval`,min(b.start)-a.start-a.`interval`,a.`value` -> from t_aimmaker a,t_aimmaker b -> where a.start<b.start -> group by a.id,a.start,a.`interval` -> having min(b.start)!=a.start+a.`interval` -> ) t -> order by start; +------+------+-------+----------+-------+ | id | name | start | interval | value | +------+------+-------+----------+-------+ | 1 | xxx | 0 | 3 | 5 | | 2 | xxx | 3 | 2 | 2 | | 3 | xxx | 5 | 3 | 2 | | 4 | xxx | 8 | 2 | 3 | +------+------+-------+----------+-------+ 4 rows in set (0.01 sec)mysql>
mysql> create view x1 as -> select name,start,`interval`,`value` from t_aimmaker -> union -> select a.name,a.start+a.`interval`,min(b.start)-a.start-a.`interval`,a.`value` -> from t_aimmaker a,t_aimmaker b -> where a.start<b.start -> group by a.id,a.start,a.`interval` -> having min(b.start)!=a.start+a.`interval`; Query OK, 0 rows affected (0.36 sec)mysql> create view x2 as -> select ( -> select count(*) from -> x1 x where start<=t.start -> ) as id,name,start,`interval`,`value` -> from x1 t -> order by start; Query OK, 0 rows affected (0.03 sec)mysql> select * from x2; +------+------+-------+----------+-------+ | id | name | start | interval | value | +------+------+-------+----------+-------+ | 1 | xxx | 0 | 3 | 5 | | 2 | xxx | 3 | 2 | 2 | | 3 | xxx | 5 | 3 | 2 | | 4 | xxx | 8 | 2 | 3 | +------+------+-------+----------+-------+ 4 rows in set (0.01 sec)mysql>
select name,start,interval,value from tth1
union all
select a.name,if(b.start<>a.start+a.interval,a.start+a.interval,b.start) as newstart,
if(b.start<>a.start+a.interval,b.start-newstart,a.start) as newinterval,a.value
from tth1 a left join tth1 b on a.id=b.id-1 and b.start<>a.start+a.interval where b.id is not null
order by start
+------+------+-------+----------+-------+
| id | name | start | interval | value |
+------+------+-------+----------+-------+
| 0 | xxx | 0 | 3 | 5 |
| 1 | xxx | 3 | 2 | 2 |
| 2 | xxx | 8 | 2 | 3 |
+------+------+-------+----------+-------+
3 rows in set (0.17 sec)mysql> select (
-> select count(*) from
-> (
-> select name,start,`interval`,`value` from t_aimmaker
-> union
-> select a.name,a.start+a.`interval`,min(b.start)-a.start-a.`interval`,a.`value`
-> from t_aimmaker a,t_aimmaker b
-> where a.start<b.start
-> group by a.id,a.start,a.`interval`
-> having min(b.start)!=a.start+a.`interval`
-> ) x where start<=t.start
-> ) as id,name,start,`interval`,`value`
-> from (
-> select name,start,`interval`,`value` from t_aimmaker
-> union
-> select a.name,a.start+a.`interval`,min(b.start)-a.start-a.`interval`,a.`value`
-> from t_aimmaker a,t_aimmaker b
-> where a.start<b.start
-> group by a.id,a.start,a.`interval`
-> having min(b.start)!=a.start+a.`interval`
-> ) t
-> order by start;
+------+------+-------+----------+-------+
| id | name | start | interval | value |
+------+------+-------+----------+-------+
| 1 | xxx | 0 | 3 | 5 |
| 2 | xxx | 3 | 2 | 2 |
| 3 | xxx | 5 | 3 | 2 |
| 4 | xxx | 8 | 2 | 3 |
+------+------+-------+----------+-------+
4 rows in set (0.01 sec)mysql>
-> select name,start,`interval`,`value` from t_aimmaker
-> union
-> select a.name,a.start+a.`interval`,min(b.start)-a.start-a.`interval`,a.`value`
-> from t_aimmaker a,t_aimmaker b
-> where a.start<b.start
-> group by a.id,a.start,a.`interval`
-> having min(b.start)!=a.start+a.`interval`;
Query OK, 0 rows affected (0.36 sec)mysql> create view x2 as
-> select (
-> select count(*) from
-> x1 x where start<=t.start
-> ) as id,name,start,`interval`,`value`
-> from x1 t
-> order by start;
Query OK, 0 rows affected (0.03 sec)mysql> select * from x2;
+------+------+-------+----------+-------+
| id | name | start | interval | value |
+------+------+-------+----------+-------+
| 1 | xxx | 0 | 3 | 5 |
| 2 | xxx | 3 | 2 | 2 |
| 3 | xxx | 5 | 3 | 2 |
| 4 | xxx | 8 | 2 | 3 |
+------+------+-------+----------+-------+
4 rows in set (0.01 sec)mysql>