leetcode-数据库题
提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档
175. 组合两个表
select p.FirstName, p.LastName, a.City, a.Statefrom Person p left join Address aon 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
BEGINset 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 rnFROM 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 nullSELECT Name AS Customers
FROM Customers
WHERE Id NOT IN (SELECT CustomerId FROM Orders);