leetcode-数据库题

原创已于 2023-04-22 22:11:46 修改 · 535 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#leetcode #sql

于 2023-04-22 19:21:13 首次发布

这篇文章展示了如何使用SQL进行数据查询，包括组合两个表、找出第二及第N高的薪水、计算分数排名、识别重复电子邮箱以及找到未订购的客户。这些例子涉及到了左连接、子查询、函数应用和窗口函数在数据分析中的应用。

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

文章目录

175. 组合两个表
176. 第二高的薪水
177. 第N高的薪水
178. 分数排名
181. 超过经理收入的员工
182. 查找重复的电子邮箱
183. 从不订购的客户

175. 组合两个表

select p.FirstName, p.LastName, a.City, a.State
 from Person p left join Address a
 on p.personid=a.personid

176. 第二高的薪水

select (select distinct salary from Employee order by salary desc  limit 1,1) as SecondHighestSalary

177. 第N高的薪水

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    set N = N-1;
  RETURN (
      # Write your MySQL query statement below.
      select (select distinct salary from Employee order by salary desc  limit N,1) as SecondHighestSalary
  );
END

178. 分数排名

SELECT score, DENSE_RANK() OVER (ORDER BY score DESC) AS `rank`
FROM (
    SELECT score, ROW_NUMBER() OVER (ORDER BY score DESC) AS rn
    FROM Scores
) s
ORDER BY `rank` ASC;

181. 超过经理收入的员工

select a.name as Employee from Employee a join Employee b 
on a.managerId = b.id where a.salary > b.salary

182. 查找重复的电子邮箱

select distinct a.email  Email from  Person a left join Person b on a.email = b.email where a.id != b.id

183. 从不订购的客户

select a.Name Customers from ( select Name, Orders.Id from Customers left join Orders on Customers.Id = Orders.CustomerId ) a where a.Id is null

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