leetcode SQL题目

组合两个表

SELECT firstName,lastName,city,state FROM Person LEFT JOIN Address ON Person.PersonId=Address.PersonId

第二高的薪水

SELECT
IFNULL(
(SELECT DISTINCT salary FROM Employee Order by salary desc LIMIT 1,1),null
) AS SecondHighestSalary

第N高的薪水

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N:=N-1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT IFNULL((SELECT DISTINCT salary FROM Employee ORDER BY salary DESC LIMIT N,1),NULL) 
  );
END

分数排名

SELECT score, (SELECT count(DISTINCT score)+1 FROM Scores S2 Where S2.score>Scores.score) AS 'rank' FROM Scores ORDER BY score DESC

连续出现的数字

SELECT DISTINCT l1.num AS 'ConsecutiveNums' FROM Logs l1,Logs l2,Logs l3 WHERE l1.id=l2.id-1 AND l2.id=l3.id-1 AND l1.num=l2.num AND l2.num=l3.num

超过经理收入的员工

SELECT e.name AS Employee FROM Employee e,Employee e2 WHERE e2.id=e.managerId AND e.salary > e2.salary

查找重复的电子邮件

SELECT DISTINCT p1.email AS Email FROM Person p1,Person p2 WHERE p1.email=p2.email AND p1.id !=p2.id

从不订购的客户

SELECT name AS Customers FROM Customers WHERE id NOT IN (SELECT DISTINCT customerId FROM Orders)

部门工资最高的员工

SELECT Department.name AS Department,e.name AS Employee,e.salary AS Salary FROM Department,Employee e WHERE Department.id=e.departmentId AND e.salary >= ALL(SELECT salary FROM Employee e2 WHERE e.departmentId = e2.departmentId)

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

SELECT Department.name AS Department, e.name AS Employee,e.salary AS Salary FROM Employee e,Department WHERE e.departmentId=Department.id AND
(SELECT COUNT(Distinct e2.salary) FROM Employee e2 WHERE e2.departmentId=e.departmentId AND e2.salary>e.salary)<3 ORDER BY e.departmentId ASC

删除重复的电子邮箱

DELETE p2 FROM Person p1,Person p2 WHERE p1.email=p2.email AND p1.id<p2.id

上升的温度

SELECT w2.id AS Id FROM Weather w1,Weather w2 WHERE DATEDIFF(w2.recordDate,w1.recordDate)=1 AND w1.Temperature < w2.Temperature

DATEDIFF（）函数用于计算两个日期的天数差

游戏玩法分析Ⅰ

SELECT player_id,MIN(event_date) AS first_login FROM Activity GROUP BY player_id

游戏玩法Ⅳ

SELECT ROUND(
(SELECT COUNT(DISTINCT b.player_id) FROM Activity b WHERE (b.player_id,b.event_date) IN
(
SELECT a.player_id,DATE(MIN(a.event_date)+INTERVAL 1 DAY) FROM Activity a GROUP BY a.player_id
))
/
(SELECT COUNT(DISTINCT player_id) FROM Activity),2
) AS fraction

至少有5名直接下属的经理

SELECT E1.name FROM Employee E1 WHERE E1.id IN (SELECT E2.managerId FROM Employee E2 GROUP BY E2.managerId HAVING COUNT(DISTINCT ID)>=5)