有如下表
StartTime EndTime Price Class
10.1 NULL 1.1 1
10.15 10.18 1.2 1
10.20 NULL 1.3 1
10.25 10.26 1.4 1如何通过SQL语句得出如下结果(传入统计时间为10.1-10.31)
StartTime EndTime Price Class
10.1 10.15 1.1 1
10.15 10.18 1.2 1
10.18 10.20 1.1 1
10.20 10.25 1.3 1
10.25 10.26 1.4 1
10.26 10.31 1.3 1
StartTime EndTime Price Class
10.1 NULL 1.1 1
10.15 10.18 1.2 1
10.20 NULL 1.3 1
10.25 10.26 1.4 1如何通过SQL语句得出如下结果(传入统计时间为10.1-10.31)
StartTime EndTime Price Class
10.1 10.15 1.1 1
10.15 10.18 1.2 1
10.18 10.20 1.1 1
10.20 10.25 1.3 1
10.25 10.26 1.4 1
10.26 10.31 1.3 1
10.1 10.15 1.1 1
10.15 10.18 1.2 1
10.18 10.20 1.1 1
10.20 10.25 1.3 1
10.25 10.26 1.4 1
10.26 10.31 1.3 1这两条的价格怎么来的?
上一行的结束时间不等于NULL并且上一行的结束时间和下一行的开始时间不相等
则取上一行的结束时间和下一行的开始时间生成一条新行
该新生的价格取该行上边最近的一个结束时间为NULL的对应的PRICE如第二条和第三条
10.15-10.18
10.20-10.25
因为10.18不为空而且不等于10.20
所以10.18-10.20组成一个新行.价格取10.18上边最近的结束时间为NULL的那行所对应的价格.即第一条的1.1
所以多出一条26-31是因为其实最后一条最不是以31结束的.而是以26结束的.所以需要补全
+------------+------------+-------+-------+
| 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>