解决方案 »
- mysql 查询(想不出方法来,看看各位的SQL)
- 求救 批量添加字段
- lock wait timeout exceeded;try restarting transaction
- MySQL在多个索引的情况下的默认排序规则是什么?能否指定默认排序索引?
- 用mysql如何不在默认盘符下创建数据库?
- mysql default 值错误
- mysql 如何利用timestamp字段来按月份分区?
- 嘿嘿,Mysql里用 LIKE '%s%' 对于中文等多字符的解决方法。。。。。
- mysql 多表查询,order by 时不使用索引
- mysql cluster 问题不理解,希望朋友帮下。
- MySQL相对SQLServer来说,性能差么?
- 我有一个字段 很明确只存取 3 个字符,用什么类型
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>