数据库基础习题14-16（含解法）

主要使用MySQL，本章核心是join的使用
题目来源 https://www.hackerrank.com/domains/sql

14、(SQL Project Planning) You are given a table, Projects, containing three columns: Task_ID, Start_Date and End_Date. It is guaranteed that the difference between the End_Date and the Start_Date is equal to 1 day for each row in the table.

If the End_Date of the tasks are consecutive, then they are part of the same project. Samantha is interested in finding the total number of different projects completed.

Write a query to output the start and end dates of projects listed by the number of days it took to complete the project in ascending order. If there is more than one project that have the same number of completion days, then order by the start date of the project.
For example,

若结束日期和开始日期相连，则是同一个project，现要求一共有多少个不同的project。

解：（1）MySQL的默认设置中有“only_full_group_by”，如果使用group by，那么select的对象必须是group by的相关对象，非常受限。所以往往会修改这个设置，操作参考去掉only_full_group_by
或者如第二段代码第一行操作。
（2）如果是从两个表中直接select，例如：
select column1
from a,b
那么查询结果是交叉的笛卡尔积（就是两个表中元素的所有组合）

SELECT Start_Date, min(End_Date)
FROM 
    (SELECT Start_Date FROM Projects WHERE Start_Date NOT IN (SELECT End_Date FROM Projects)) a,
    (SELECT End_Date FROM Projects WHERE End_Date NOT IN (SELECT Start_Date FROM Projects)) b 
WHERE Start_Date < End_Date
GROUP BY Start_Date 
ORDER BY DATEDIFF(min(End_Date), Start_Date), Start_Date

或者不使用min：

SET sql_mode = '';
SELECT Start_Date, End_Date
FROM 
    (SELECT Start_Date FROM Projects WHERE Start_Date NOT IN (SELECT End_Date FROM Projects)) a,
    (SELECT End_Date FROM Projects WHERE End_Date NOT IN (SELECT Start_Date FROM Projects)) b 
WHERE Start_Date < End_Date
GROUP BY Start_Date 
ORDER BY DATEDIFF(End_Date, Start_Date), Start_Date

15、（Placements）You are given three tables: Students, Friends and Packages. Students contains two columns: ID and Name. Friends contains two columns: ID and Friend_ID (ID of the ONLY best friend). Packages contains two columns: ID and Salary. Write a query to output the names of those students whose best friends got offered a higher salary than them. Names must be ordered by the salary amount offered to the best friends. It is guaranteed that no two students got same salary offer. 查询朋友的工资比自己高的人的名字，按朋友工资排序。

select s.name
from students s join friends f on s.id=f.id
join (select f.id as f_id,p.salary as p_s from friends f join packages p on f.id=p.id)fs1 on f.id=fs1.f_id
join (select f.friend_id as ff_id,p.salary as p_s from friends f join packages p on f.friend_id=p.id)fs2 on f.friend_id=fs2.ff_id
where fs1.p_s<fs2.p_s
order by fs2.p_s

16、（Symmetric Pairs）
You are given a table, Functions, containing two columns: X and Y.

Two pairs (X1, Y1) and (X2, Y2) are said to be symmetric pairs if X1 = Y2 and X2 = Y1.

Write a query to output all such symmetric pairs in ascending order by the value of X.

For example:


解：注意where和group by的互相补充，这题用where就很难完成

select f.x,f.y
from functions f 
join (select y as x1,x as y1 from functions)functions2
on f.x=functions2.x1 and f.y=functions2.y1
group by f.x,f.y
having f.x<f.y or count(f.x)>1
order by f.x