力扣

1. 力扣 181. 超过经理收入的员工

用时: 381ms, 内存: 0

SELECT e1.name as Employee
FROM employee as e1, employee as e2
WHERE e1.managerid = e2.id AND e1.salary > e2.salary;

自连接查询:

用时: 445ms, 内存: 0

SELECT e1.name as Employee
FROM employee as e1 INNER JOIN employee as e2
ON e1.managerid = e2.id AND e1.salary > e2.salary;

2. 力扣 182. 查找重复的电子邮箱

分组查询:

用时: 382ms, 内存: 0

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

3. 力扣 183. 从不订购的客户

左外连接:

用时: 600ms

SELECT Customers.Name AS Customers
FROM Customers LEFT JOIN Orders
ON Customers.Id = Orders.CustomerId
WHERE Orders.Id IS NULL;

NOT IN:

用时: 561ms

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

4. 力扣 175. 组合两个表

左外连接:
用时: 380ms

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

5. 力扣 197. 上升的温度

内连接:
用时: 466ms

SELECT w1.id
FROM Weather AS w1 INNER JOIN Weather AS w2
ON DATEDIFF(w1.recordDate, w2.recordDate) = 1 AND w1.Temperature > w2.Temperature;

6. 620. 有趣的电影

用时: 219ms

SELECT * FROM cinema
WHERE id % 2 = 1 AND description != 'boring'
ORDER BY rating DESC;

7. 596. 超过5名学生的课

用时: 285ms

SELECT class
FROM courses
GROUP BY class
HAVING COUNT(DISTINCT student) >= 5;