LeetCode_SQL自用

175组合两个表（LEFT JOIN）

SELECT p.FirstName,p.LastName,a.City,a.State 
FROM Person p 
LEFT JOIN Address a 
ON p.PersonId = a.PersonId

理解LEFT JOIN 主表是哪个

---

176.第二高的薪水(ORDER BY /IFNULL)

SELECT 
    IFNULL(
        (SELECT DISTINCT Salary  
        FROM Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1),NULL) AS SecondHighestSalary

ISNULL(expr):如果expr为空返回1 ，否则0

IFNULL(expr1，expr2):如果expr1为空，返回expr2,不为空返回expr1

---

177.第N高的薪水

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    SET N = N - 1;
    RETURN (
        SELECT IFNULL((SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT N,1),NULL);
END
    

自定义函数

---

178分数排名

SELECT Score,DENSE_RANK() OVER (ORDER BY Score DESC) AS "Rank"
FROM Scores

窗口函数

182查找重复的电子邮箱

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

分组查询 聚合函数

183从不订购的客户

SELECT Name AS 'Customers'
FROM Customers
WHERE Id NOT IN(
    SELECT CustomerId
    FROM Orders
)

NOT IN

184部门工资最高的员工

SELECT d.Name AS 'Department',e.Name AS 'Employee',Salary
FROM Employee e 
JOIN Department d 
ON e.DepartmentId = d.Id
WHERE (e.DepartmentId,Salary) IN(
    SELECT DepartmentId,MAX(Salary)
    FROM Employee
    GROUP BY DepartmentId
)

注意 JOIN 之后加ON！！！子查询其实不需要第二张表

185部门工资前三高的所有员工

SELECT D.Name AS 'Department',A.Name AS 'Employee',A.Salary
FROM(
    SELECT Name,Salary,DepartmentId,
    DENSE_RANK() OVER(PARTITION BY DepartmentId ORDER BY Salary DESC) AS 'Ranking'
    FROM Employee)A 
JOIN Department D
ON D.Id = A.DepartmentId
WHERE Ranking <= 3

1454活跃用户

SELECT *
FROM Accounts
WHERE id IN(
    SELECT l1.id
    FROM Logins l1
    JOIN Logins l2
    ON l1.id = l2.id
    AND DATEDIFF(l2.login_date,l1.login_date) BETWEEN 0 AND 4
    GROUP BY l1.id,l1.login_date
    HAVING COUNT(DISTINCT l2.login_date)=5
    ) 
ORDER BY id

自连接

类似的还有以下

180连续出现的数字

SELECT DISTINCT l1.Num AS ConsecutiveNums
FROM Logs l1
JOIN Logs l2
ON l1.Num = l2.Num 
AND l2.id - l1.id BETWEEN 0 AND 2
GROUP BY l1.id,l1.Num
HAVING COUNT(*)=3

181超过经理收入的员工

SELECT e1.Name AS Employee
FROM Employee e1
JOIN Employee e2 ON e2.Id= e1.ManagerId
WHERE e1.Salary > e2.Salary

注意连接条件 Id 和 ManagerId 哪个是e1的

182查找重复的电子邮箱

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