Table: Failed
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| fail_date | date |
+--------------+---------+
该表主键为 fail_date。
该表包含失败任务的天数.
Table: Succeeded
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| success_date | date |
+--------------+---------+
该表主键为 success_date。
该表包含成功任务的天数.
系统 每天 运行一个任务。每个任务都独立于先前的任务。任务的状态可以是失败或是成功。
编写一个 SQL 查询 2019-01-01 到 2019-12-31 期间任务连续同状态 period_state 的起止日期(start_date 和 end_date)。即如果任务失败了,就是失败状态的起止日期,如果任务成功了,就是成功状态的起止日期。
最后结果按照起始日期 start_date 排序
查询结果样例如下所示:
Failed table:
+-------------------+
| fail_date |
+-------------------+
| 2018-12-28 |
| 2018-12-29 |
| 2019-01-04 |
| 2019-01-05 |
+-------------------+
Succeeded table:
+-------------------+
| success_date |
+-------------------+
| 2018-12-30 |
| 2018-12-31 |
| 2019-01-01 |
| 2019-01-02 |
| 2019-01-03 |
| 2019-01-06 |
+-------------------+
Result table:
+--------------+--------------+--------------+
| period_state | start_date | end_date |
+--------------+--------------+--------------+
| succeeded | 2019-01-01 | 2019-01-03 |
| failed | 2019-01-04 | 2019-01-05 |
| succeeded | 2019-01-06 | 2019-01-06 |
+--------------+--------------+--------------+
结果忽略了 2018 年的记录,因为我们只关心从 2019-01-01 到 2019-12-31 的记录
从 2019-01-01 到 2019-01-03 所有任务成功,系统状态为 "succeeded"。
从 2019-01-04 到 2019-01-05 所有任务失败,系统状态为 "failed"。
从 2019-01-06 到 2019-01-06 所有任务成功,系统状态为 "succeeded"。
来源:力扣(LeetCode)
链接:https://leetcode-cn.com/problems/report-contiguous-dates
著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。
审题:编写一个 SQL 查询 2019-01-01 到 2019-12-31 期间任务连续同状态 period_state 的起止日期(start_date 和 end_date)。即如果任务失败了,就是失败状态的起止日期,如果任务成功了,就是成功状态的起止日期。
思考:查询任务成功或者失败,需要单独分开看成功失败问题,成功的从第一个向下加一天,无法向下加的时候就是结果。
如果第一天就无法向下加,就是这一天。
成功和失败道理一样,但是需要按照起始日期排序。
解题:
解题思路
1.两部分数据并起来
2.按日期排序后遍历,分配group_id,连续成功或失败group_id一致
3.按group_id分组,取最大和最小日期即为end_date、start_date
-- Write your MySQL query statement below
SELECT if(task_result = 0, 'failed', 'succeeded') AS period_state
, MIN(date) AS start_date, MAX(date) AS end_date
FROM (
SELECT date, task_result
, @group_id := if(@last_result = task_result, @group_id, @group_id + 1) AS group_id
, @last_result := task_result
FROM (
SELECT fail_date AS date, 0 AS task_result
FROM Failed
UNION
SELECT success_date AS date, 1 AS task_result
FROM Succeeded
) a, (
SELECT @group_id := 0, @last_result := 0
) temp
WHERE date BETWEEN '2019-01-01' AND '2019-12-31'
ORDER BY date ASC
) b
GROUP BY group_id
ORDER BY start_date ASC
方法二:
select * from
((select 'succeeded' period_state, min(success_date)start_date, max(success_date)end_date
from (select success_date, if(datediff(success_date, @prev)=1, @count, @count:=@count+1) num, @prev:=success_date
from Succeeded, (select @prev:=null, @count:=0)i
where year(success_date)='2019') t
group by t.num)
union all
(select 'failed' period_state, min(fail_date)start_date, max(fail_date)end_date
from (select fail_date, if(datediff(fail_date, @prev)=1, @count, @count:=@count+1) num, @prev:=fail_date
from Failed, (select @prev:=null, @count:=0)i
where year(fail_date)='2019') t
group by t.num)) t
order by start_date;
知识点: