今天下午,想重新温习下SQL,就刷了下LeetCode上有关Database的简单题和中等题。
- 简单题
1. Employees Earning More Than Their Managers
1 2 3 |
SELECT a.Name FROM Employee a INNER JOIN Employee b ON a.ManagerId = b.Id WHERE a.Salary > b.Salary |
1 2 3 4 |
SELECT Email FROM Person GROUP BY Email HAVING COUNT(Email) > 1 |
1 2 |
SELECT FirstName, LastName, City, State FROM Person p LEFT JOIN Address a ON p.PersonId = a.PersonId |
1 2 3 4 |
SELECT Name AS Customers FROM Customers WHERE Id NOT IN (SELECT CustomerId FROM Orders) |
1 2 3 4 5 |
SELECT Name AS Customers FROM Customers WHERE NOT EXISTS (SELECT CustomerId FROM Orders WHERE Orders.CustomerId = Customers.Id) |
1 2 3 |
SELECT b.Id FROM Weather a RIGHT JOIN Weather b ON DATEDIFF(b.Date, a.Date) = 1 WHERE a.Temperature < b.Temperature |
1 2 3 |
SELECT b.Id FROM Weather a RIGHT JOIN Weather b ON TO_DAYS(b.Date) - TO_DAYS(a.Date) = 1 WHERE a.Temperature < b.Temperature |
1 2 3 |
SELECT b.Id FROM Weather a RIGHT JOIN Weather b ON DATE_SUB(a.Date, INTERVAL -1 DAY) = b.Date WHERE a.Temperature < b.Temperature |
1 2 3 4 5 6 |
SELECT IFNULL(( SELECT DISTINCT Salary AS SecondHighestSalary FROM Employee ORDER BY Salary DESC LIMIT 1, 1 ), NULL) |
1 2 3 4 5 6 |
SELECT ( SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1, 1 ) AS SecondHighestSalary |
1 2 3 4 5 6 |
SELECT ( SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1 ) AS SecondHighestSalary |
1 2 3 4 5 6 |
DELETE FROM Person WHERE Id NOT IN (SELECT Id FROM (SELECT MIN(Id) AS Id FROM Person GROUP BY Email ) t) |
- 中等题
1 2 3 4 |
SELECT DISTINCT l1.num FROM Logs l1 JOIN Logs l2 ON l1.id = l2.id - 1 JOIN Logs l3 ON l1.id = l3.id - 2 WHERE l1.num = l2.num AND l2.num = l3.num |
9. Rank Scores
1 2 3 4 5 6 7 |
SELECT Score, ( SELECT COUNT(DISTINCT Score) FROM Scores WHERE Score >= s.Score ) AS Rank FROM Scores s ORDER BY Score DESC |
1 2 3 4 5 6 7 8 |
SELECT D.Name AS Department, E.Name AS Employee, E.Salary FROM Employee E, (SELECT DepartmentId, MAX(Salary) AS max FROM Employee GROUP BY DepartmentId ) T, Department D WHERE E.DepartmentId = T.DepartmentId AND E.Salary = T.max AND E.DepartmentId = D.id |
1 2 3 4 5 6 7 8 |
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT BEGIN DECLARE M INT; SET M = N-1; RETURN ( select IFNULL((select distinct Salary from Employee order by Salary DESC limit M, 1), null) ); END |