SQL刷题，再次接近崩溃，自己写的和高手写的为什么差那么多（hackerrank-sql-SQL Project Planning）

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