hackerrank-sql-SQL Project Planning
题目
我的解法
用的窗口函数
select t6.Start_Date, t6.end_date_new
from
(select t5.Start_Date, t5.end_date_new, datediff(end_date_new, t5.Start_Date) as num
from
(select t4.Start_Date,
case
when t4.End_Date<=t4.c then t4.c
when t4.c is null then t4.End_Date
end as end_date_new
from
(select *, lead(t3.a,1) over(order by t3.Start_Date) as c
from
(select *
from
(select t1.*, datediff(t1.Start_Date, t1.a) as b
from
(select Start_Date, End_Date,
lag(End_Date, 1) over(order by Start_Date) as a
from Projects) t1) t2
where t2.b is null or t2.b!=0) t3) t4) t5) t6
order by t6.num, t6.Start_Date;
更优的解法
逻辑清晰,基本就是简单的语句,只能膜拜了
/* MIN(end_date) means for the particular start_date, we get the closest end date */
SELECT start_date, MIN(end_date)
FROM
/* Choose start dates that are not end dates of other projects */
(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
/* This makes sure we only choose end dates that fall after the start date */
WHERE a.start_date < b.end_date
GROUP BY start_date
/* DATEDIFF calculates the number of days between start date and end date*/
ORDER BY DATEDIFF(MIN(end_date), start_date), start_date