解决方案 »
- 在一个表中随机取出5行,请问哪种方法是最节省资源的
- MYSQL免安装文件夹里哪些东西可以不要??版本:5.0.22-win32
- 如何数出表a中的个数成为一个临时表,SELECT INTO 吗?如何写这个语句啊?~~~~~~~~~~~~~~~~
- FC5下mysql5启动问题,说找不到mysql-bin.index
- 这个创建表的代码哪里出问题了啊?
- mysql连接的问题
- 关于Mysql中一个复合查询的问题
- ASP如何使用MSSQL
- 在同一台机器上运行300+个mysql实例会不会有什么问题
- mysql 是否有只去除某列的语句
- 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>