LeetCode SQL 刷题日记-DAY2

LeetCode SQL 刷题日记-DAY2

硕士在读数据分析小小白一枚，记录自己学习进步的点点滴滴~希望可以坚持下去！拿到理想Offer！数据人冲冲冲！！！
（刷题顺序随机）

(1)182. 查找重复的电子邮箱（简单）

编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。
思路
联立两表，EMAIL 值相同，但id不同

having用于分组后的过滤，放到group by 后面，常和聚合函数一起用 where 用于全表数据集的筛选，在分组动作的前面
解法1 (时间开销大)

# Write your MySQL query statement below
SELECT DISTINCT t1.Email
FROM Person t1 JOIN Person t2
ON t1.Email = t2.Email
AND t1.Id != t2.Id;

执行结果

解法2

SELECT Email FROM Person 
GROUP BY Email 
HAVING COUNT(Email)>1;

解法3 （最高效，先计数再查找）

SELECT Email 
FROM ( SELECT COUNT(1) AS T, Email FROM Person GROUP BY Email ) R
WHERE R.T>1;

(2)183. 从不订购的客户（简单）

某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。
思路
比较，left join 联立两表，比较Order.id 为空的值
判断空值 is null 或者 is not null
解法1

# Write your MySQL query statement below
SELECT t1.Name as Customers 
FROM Customers t1 LEFT JOIN Orders t2
ON t1.Id = t2.CustomerId
WHERE t2.Id is NULL; 

执行结果

(3)184. 部门工资最高的员工（中等）

Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。
编写一个 SQL 查询，找出每个部门获得最高工资的所有员工。
思路
第一思路：max() group by 多表查询
错误1：同样最高的Salary只输出了一个；考虑dense_rank（）？
yep！继续用万能over开窗函数！！！RANK()/DENSE_RANK()都可以~
解法1

# Write your MySQL query statement below
SELECT S.Department, S.Employee, S.Salary
FROM(
    SELECT D.Name Department, E.Name Employee, E.Salary,
    DENSE_RANK() OVER(PARTITION BY E.DepartmentId ORDER BY E.Salary DESC) RN # rank_number
    FROM Employee E LEFT JOIN Department D
    ON E.DepartmentId = D.Id
) S
WHERE S.RN = 1
AND S.Department IS NOT NULL;

执行结果

拓展— 每个部门前2高

SELECT S.NAME, S.EMPLOYEE, S.SALARY
  FROM (SELECT D.NAME,
               T.NAME EMPLOYEE,
               T.SALARY,
               DENSE_RANK() OVER(PARTITION BY T.DEPARTMENTID ORDER BY T.SALARY DESC) RN
          FROM EMPLOYEE T
          LEFT JOIN DEPARTMENT D
            ON T.DEPARTMENTID = D.ID) S
 WHERE S.RN <= 2
 AND S.Department IS NOT NULL;

拓展2— 每个部门第1，3高

SELECT S.NAME, S.EMPLOYEE, S.SALARY
  FROM (SELECT D.NAME,
               T.NAME EMPLOYEE,
               T.SALARY,
               DENSE_RANK() OVER(PARTITION BY T.DEPARTMENTID ORDER BY T.SALARY DESC) RN
          FROM EMPLOYEE T
          LEFT JOIN DEPARTMENT D
            ON T.DEPARTMENTID = D.ID) S
 WHERE S.RN = 1 OR S.RN = 3
 AND S.Department IS NOT NULL;

(4)185. 部门工资前三高的所有员工（困难）

Employee 表包含所有员工信息，每个员工有其对应的工号 Id，姓名 Name，工资 Salary 和部门编号 DepartmentId 。
编写一个 SQL 查询，找出每个部门获得前三高工资的所有员工。例如，根据上述给定的表，查询结果应返回：
思路
开窗函数
解法

# Write your MySQL query statement below
SELECT S.Department, S.Employee, S.Salary
FROM(
    SELECT D.Name Department, E.Name Employee, E.Salary Salary,
    DENSE_RANK() OVER(PARTITION BY E.DepartmentId ORDER BY E.Salary DESC) RN
    FROM Employee E LEFT JOIN Department D
    ON E.DepartmentId = D.Id
) S
WHERE S.RN <=3
AND S.Department IS NOT NULL;

执行结果

(5)626. 换座位（中等）

小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。
其中纵列的 id 是连续递增的
小美想改变相邻俩学生的座位。
你能不能帮她写一个 SQL query 来输出小美想要的结果呢？
思路
*万能开窗函数 或者 case when *
解法1—case when

SELECT (CASE
            WHEN MOD(id,2)=1 AND id = (SELECT COUNT(*) FROM seat) THEN id # 最后一个数为奇数
            WHEN MOD(id,2)=1 THEN id+1 # 奇数+1
            ELSE id-1 # 偶数-1
        END) AS id, student
FROM seat 
ORDER BY id;

解法2—row_number() over()
^ 位异或
a^b，当a、b相同时返回0，不同时返回1
a^1时，a转换为二进制，其他位与0异或，值不变，最后一位0 ^1=1，1 ^1=0
偶数异或会+1，奇数异或会-1
比如 4 ^1=5 5^1=4

!（逻辑非）
! 运算符不能与其他运算符一起使用。例如，不能将 ! 和 > 运算符组合为 !>. 运算符

&（位与）
对两个整数值执行“位与”运算。它会将第一个操作数的每一位与第二个操作数中对应的每一位进行比较。如果两位都是 1，则相应的结果位设置为1。
否则，相应的结果位设置为 0。
两个条件都必须是有符号整数类型，或者都必须是无符号整数类型。

select rank() over(order by (id-1)^1) as id,student from seat

(6)262. 行程和用户（困难）

写一段 SQL 语句查出 “2013-10-01” 至 “2013-10-03” 期间非禁止用户（乘客和司机都必须未被禁止）的取消率。非禁止用户即 Banned 为 No 的用户，禁止用户即 Banned 为 Yes 的用户。

取消率 的计算方式如下：(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)。

返回结果表中的数据可以按任意顺序组织。其中取消率 Cancellation Rate 需要四舍五入保留 两位小数 。
解法

select Request_at Day,Round(  avg(Status!='completed')  , 2) `Cancellation Rate`
from Trips t
where t.Client_Id in (select Users_Id from Users where Banned='No')
and t.Driver_Id in (select Users_Id from Users where Banned='No')
and Request_at in ('2013-10-01','2013-10-02','2013-10-03')
group by Request_at

结果