提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档
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);