leetcode SQL题目

Easy

175.组合两个表 左连接

select Person.FirstName, Person.LastName, Address.City, Address.State from Person left join Address on Person.PersonId = Address.PersonId

176.第二高的薪水 先选出最大的，再选出第二大的

SELECT MAX(Salary) SecondHighestSalary FROM Employee WHERE Salary<(SELECT MAX(Salary) FROM Employee) //where条件里是薪水小于最大薪水

181.超过经理收入的员工 表自身按不同属性进行内连接 当一个表要比较自身的不同列的时候可以对自身进行表连接

SELECT e.Name as Employee FROM Employee as e JOIN Employee as m ON e.ManagerId = m.Id WHERE e.Salary>m.Salary

182.查找重复的电子邮箱 在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。 HAVING 子句可以让我们筛选分组后的各组数据。

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

183.从不订购的客户

SELECT Name as Customers from Customers LEFT JOIN Orders ON Customers.Id = Orders.CustomerId WHERE Orders.CustomerId IS NULL;

196.删除重复的电子邮件 参考： https://leetcode.com/problems/delete-duplicate-emails/discuss/55614/A-skillful-mysql-solution-avoid-"-select-and-update-conflict"

https://leetcode.com/problems/delete-duplicate-emails/discuss/55553/Simple-Solution

197.上升的温度 表自身连接

SELECT t1.Id FROM Weather t1 INNER JOIN Weather t2 ON TO_DAYS(t1.Date) = TO_DAYS(t2.Date) + 1 WHERE t1.Temperature > t2.Temperatur

595.大的国家 参考： https://leetcode.com/problems/big-countries/discuss/103561/Union-and-OR-and-the-Explanation

用Union代替Or，加快速度（适用于查询不同列的情况，如果是同一列则OR快）

596.超过5名学生的课 普通的group by,每组统计的时候用distinct去重

627.交换工资 https://leetcode.com/problems/swap-salary/discuss/104716/Short-and-Simple

https://leetcode.com/problems/swap-salary/discuss/104722/Simple-and-short-with-IF

Medium

177.第N高的薪水

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

LIMIT m OFFSET n 表示从第n个记录开始取m条（第一个记录编号是0） 参考： https://leetcode.com/problems/nth-highest-salary/discuss/53041/Accpted-Solution-for-the-Nth-Highest-Salary

178.分数排名

SELECT Scores.Score, COUNT(Ranking.Score) AS RANK FROM Scores //Score表 , ( SELECT DISTINCT Score FROM Scores ) Ranking //Rankin表，表中存放所有不重复的成绩 //FROM对两个表进行了笛卡尔积，假设一共有4个不同的成绩，score表中的每一行被复制4次，并添加成绩一列 WHERE Scores.Score <= Ranking.Score //用来计算Score中每个成绩分别对应的名次，假设当前行的成绩是2，所有的成绩是1,2，3,4，那么他的名次就是第3名，他小于1,2，3,4，中的三个数 GROUP BY Scores.Id, Scores.Score ORDER BY Scores.Score DESC;

180.连续出现的数字 参考：（好理解） https://leetcode.com/problems/consecutive-numbers/discuss/53418/Simple-solution

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 //取Id相连的行 and l1.Num=l2.Num and l2.Num=l3.Num //取数字相同的行

参考：（时间占用最小，至于为什么不太清楚） https://leetcode.com/problems/consecutive-numbers/discuss/282107/Simple-MySql-Tuple-Check.-Fast

SELECT Distinct(Num) as ConsecutiveNums FROM Logs WHERE (ID+1,Num) IN (Select Id, Num From Logs) AND (ID+2,Num) IN (Select Id, Num From Logs) //用两次表查询

184.部门工资最高的员工 参考： https://leetcode.com/problems/department-highest-salary/discuss/53607/Three-accpeted-solutions 这里取的第1,3种方法

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 SELECT D.Name AS Department ,E.Name AS Employee ,E.Salary from Employee E, Department D WHERE E.DepartmentId = D.id AND (DepartmentId,Salary) in (SELECT DepartmentId,max(Salary) as max FROM Employee GROUP BY DepartmentId) //将部门以及最高工资提取成一个表

626.换座位

/* get all the even numbered rows as odd numbered rows */ SELECT s1.id - 1 as id, s1.student FROM Seat s1 WHERE s1.id MOD 2 = 0 //先凑出偶数id的 UNION /* get all the odd numbered rows as even numbered rows */ SELECT s2.id + 1 as id, s2.student FROM Seat s2 WHERE s2.id MOD 2 = 1 AND s2.id != (SELECT MAX(id) FROM Seat) //凑出奇数id的，但是不处理最后一行，因为每次都是以1开头的，那么如果最后一个以奇数结尾，说明一共有奇数行，则最后一行不需要处理 /* Just don't get the last row as we will handle it in the next UNION */ UNION /* get the last row if odd and don't change the id value */ SELECT s3.id, s3.student FROM Seat s3 WHERE s3.id MOD 2 = 1 AND s3.id = (SELECT MAX(id) FROM Seat) //如果是奇数行，直接取最后一行奇数 /* Order the result by id */ ORDER BY id ASC;

参考： https://leetcode.com/problems/exchange-seats/discuss/104707/Using-two-UNION-operators