56,679
社区成员
发帖
与我相关
我的任务
分享
mysql> select * from t_maskdata;
+------------+------------+-------+-------+
| StartTime | EndTime | Price | Class |
+------------+------------+-------+-------+
| 2009-10-01 | NULL | 1.1 | 1 |
| 2009-10-15 | 2009-10-18 | 1.2 | 1 |
| 2009-10-20 | NULL | 1.3 | 1 |
| 2009-10-25 | 2009-10-26 | 1.4 | 1 |
+------------+------------+-------+-------+
4 rows in set (0.00 sec)
mysql>
mysql> set @x1='2009-10-01';
Query OK, 0 rows affected (0.00 sec)
mysql> set @x2='2009-10-31';
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select t.StartTime,
-> COALESCE(
-> b.EndTime,
-> (select min(StartTime) from t_maskdata where StartTime> t.StartTime),
-> @x2
-> ) as EndTime,
-> COALESCE(
-> b.Price,
-> (select Price from t_maskdata where StartTime<t.StartTime and EndTime is null order by StartTime desc limit 1)
-> ) as Price,
-> COALESCE(
-> b.Class,
-> (select Class from t_maskdata where StartTime<t.StartTime and EndTime is null order by StartTime desc limit 1)
-> ) as Class
-> from (
-> select StartTime from t_maskdata
-> union
-> select EndTime from t_maskdata where EndTime is not null
-> union
-> select @x1
-> ) t left join t_maskdata b on t.StartTime=b.StartTime
-> order by 1;
+------------+------------+-------+-------+
| StartTime | EndTime | Price | Class |
+------------+------------+-------+-------+
| 2009-10-01 | 2009-10-15 | 1.1 | 1 |
| 2009-10-15 | 2009-10-18 | 1.2 | 1 |
| 2009-10-18 | 2009-10-20 | 1.1 | 1 |
| 2009-10-20 | 2009-10-25 | 1.3 | 1 |
| 2009-10-25 | 2009-10-26 | 1.4 | 1 |
| 2009-10-26 | 2009-10-31 | 1.3 | 1 |
+------------+------------+-------+-------+
6 rows in set (0.01 sec)
mysql>