1차 중간 과정
select *
from (
select B.task_id, B.start_date, B.end_date, B.flag
,case
when @prev_value = B.flag then @row_num := @row_num + 1
else @row_num := 0 -- first row : '' != flag(1) -> 0
end as rn
, @prev_value := B.flag
from (select A.*
,(SELECT @row_num := 0) x
,(SELECT @prev_value := '') y
from (
select A.*
, case when B.start_date is not null then 1 else 0 end as flag
from projects A
left join Projects B
on B.start_date = A.end_date
order by 2,3
) A
) B
)C
where C.rn = 0
mysql 의 row_number 구현으로 아래 과정까지 왔다.
2개 행씩 끊어보면 하나의 프로젝트 기간인 것이다.
/* Choose start dates that are not end dates of other projects
(if a start date is an end date, it is part of the same project) */
SELECT Start_Date, MIN(End_Date)
FROM
(SELECT Start_Date
FROM Projects
WHERE Start_Date NOT IN (
SELECT End_Date FROM Projects
)
) a,
/* Choose end dates that are not end dates of other projects */
(SELECT end_date
FROM PROJECTS
WHERE end_date NOT IN (
SELECT start_date FROM PROJECTS
)
) b
/* At this point, we should have a list of start dates and end dates that don't necessarily correspond with each other */
/* This makes sure we only choose end dates that fall after the start date,
and choosing the MIN means for the particular start_date,
we get the closest end date that does not coincide with the start of another task */
where start_date < end_date
GROUP BY start_date
ORDER BY datediff(start_date, MIN(end_date)) DESC, start_date