leetcode中的sql

阅读原文时间：2023年07月09日阅读：1

组合两张表, 题目很简单, 主要考察JOIN语法的使用。唯一需要注意的一点, 是题目中的这句话, "无论 person 是否有地址信息"。说明即使Person表, 没有信息我们也需要将Person表的内容进行返回。所以我选择使用左外查询, 当然你也可以选择RIGHT OUTER JOIN, 这取决于你查询语句的写法。

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

第二高的薪水, 题目本身并不难, 但是请注意, 题目中的描述"如果不存在第二高的薪水，那么查询应返回 null", 这意味着, 如果SQL没有查询到结果, SQL本身需要一个默认的返回值。如何才能做到, 即使没有结果也返回一个值。

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

![](https://article.cdnof.com/2307/e83cf8e5-d42b-4eb4-b481-1390ba520fe9.png)

select s1.Score,count(distinct s2.Score) as Rank
from Scores as s1 inner join Scores as s2
on s1.Score <= s2.Score
group by s1.Id
order by s1.Score desc

SELECT emp1.Name AS Employee

FROM Employee AS emp1, Employee AS emp2 WHERE emp1.ManagerId = emp2.Id AND emp1.Salary > emp2.Salary

同样是非常简单的一道题目, 唯一可能需要了解的就是, GROUP BY Person.Email的字句, 可以对Person.Email字段起到去重的作用

SELECT Person.Email AS Email FROM Person GROUP BY Person.Email HAVING COUNT(Person.Email) > 1 ------------------------------- DELETE p1 FROM Person p1 inner join Person p on p1.Email = p2.Email AND p1.Id > p2.Id

SELECT Customers.Name AS Customers FROM Customers WHERE Customers.Id NOT IN ( SELECT Orders.CustomerId FROM Orders ) -------------------------------------------- select Name as Customers from Customers where Id not in (select CustomerId from Orders)

部门工资最高的员工, 在对这一题目进行解答之前。我们需要明确知道一点。"除聚合, 计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出"。也就是说, 我们并不能在求, 每一个部门工资的Max最大值的时候, 把员工的id也计算出来。

对于这道题目，我们解答的步骤分为两步, 1. 求出每一个部门对应的最高工资, 并且将结果存储为派生表 2. 根据员工的部门id, 以及员工的工资, 与派生表联结, 比较对应员工的工资是否等于派生表的部门的最高工资。如果等于, 此人的工资就是部门的最高工资

![](https://article.cdnof.com/2307/3b20a2f4-eaa6-47e1-a0e6-baa14b188408.png)

select d.Name as Department,e.Name as Employee, e.Salary
from Employee as e inner join Department as d
on e.DepartmentId = d.Id
where (e.DepartmentId,e.salary) in (select DepartmentId,max(Salary) from Employee group by DepartmentId)

DELETE p1 FROM Person p1 inner join Person p2 on p1.Email = p2.Email AND p1.Id > p2.Id

SELECT w1.Id AS Id FROM Weather AS w1 INNER JOIN Weather AS w2 ON w1.RecordDate = DATE_SUB(w2.RecordDate,INTERVAL -1 DAY) WHERE w1.Temperature > w2.Temperature

SELECT World.Name AS Name, World.population AS population, World.area AS area FROM World WHERE World.population > 25000000 OR World.area > 3000000

超过5名学生的课, 本道题目注意考察点在于对GROUP BY去重效果的认知上。

首先子查询的采用嵌套分组。首先使用课程分组然后根据学生进行分组。可以有效去除课程, 学生重复的行。为什么不直接使用学生分组呢？因为这样做会丢失学生的课程信息。在外层的查询中只需要查找中COUNT大于5的课程即可

**`SELECT ClassLength.class FROM (

排除了学生和课程重复的行

SELECT courses.class AS class  
FROM courses  
GROUP BY courses.class, courses.student

) AS ClassLength
GROUP BY ClassLength.class
HAVING COUNT(ClassLength.class) >= 5`**

------------------------------------------``select class from courses group by class having count(distinct(student))>=5; 优先级：from--on--join--_where--group by--with--having_--select--distinct--order by