关于多条数据中,在多条开始时间结束时间中取最大交集
数据如下
id | 开始时间 | 结束时间 |
---|---|---|
1 | 2017-03-14 | 2017-04-19 |
2 | 2016-01-19 | 2018-04-19 |
3 | 2017-04-19 | 2019-04-19 |
SQL
SELECT id,
(SELECT start_date FROM `test` WHERE end_date>CURDATE() ORDER BY start_date ASC LIMIT 1) AS sTime,
(SELECT end_date FROM `test` WHERE end_date>CURDATE() ORDER BY end_date DESC LIMIT 1) AS eTime
FROM test