MySQL 过程式编程，把一些间隔为1天的区间拼接起来

题目地址 https://www.hackerrank.com/challenges/sql-projects/problem?isFullScreen=true

/*
Enter your query here.
*/

-- order by consec_days asc,task_start_date asc
-- mysql progress oriented programming



WITH answer_dates AS (
    SELECT
        start_date,
        end_date,
        IF(start_date = @prev_date , @consec_days := @consec_days + 1, @consec_days := 1) AS consec_days,
        @prev_date := end_date,
        IF(@consec_days = 1, @task_start_date := start_date,@task_start_date := @task_start_date) as task_start_date
    FROM
        (SELECT @prev_date := NULL, @consec_days := 1, @task_start_date := NULL) vars,
        (SELECT start_date,end_date FROM Projects ORDER BY start_date) ordered_dates
)


select task_start_date, end_date as task_end_date
from
(
    select * ,
    row_number() over(partition by task_start_date order by consec_days desc ) as rn
    from answer_dates
)tmp1
where rn=1
order by consec_days asc,task_start_date asc