175.组合两个表
编写一个SQL查询来报告 Person 表中每个人的姓、名、城市和州。如果 personId 的地址不在 Address 表中,则报告为空 null 。
https://leetcode.cn/problems/combine-two-tables/solution/zu-he-liang-ge-biao-by-leetcode/
# Write your MySQL query statement below
select FirstName, LastName, City, State
from Person left join Address
on Person.PersonId = Address.PersonId
;
176.第二高的薪水
编写一个 SQL 查询,获取并返回 Employee 表中第二高的薪水 。如果不存在第二高的薪水,查询应该返回 null 。
https://leetcode.cn/problems/second-highest-salary/solution/di-er-gao-de-xin-shui-by-leetcode/
# Write your MySQL query statement below
select max(Salary) SecondHighestSalary
from Employee
where Salary < (select max(Salary) from Employee)
177.第N高的薪水
https://leetcode.cn/problems/nth-highest-salary/solution/mysql-zi-ding-yi-bian-liang-by-luanz/
编写一个SQL查询来报告 Employee 表中第 n 高的工资。如果没有第 n 个最高工资,查询应该报告为 null 。
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
SELECT
DISTINCT salary
FROM
(SELECT
salary, dense_rank() over(ORDER BY salary DESC) AS rnk
FROM
employee) tmp
WHERE rnk = N
);
END
178.分数排名
编写 SQL 查询对分数进行排序。排名按以下规则计算:
分数应按从高到低排列。
如果两个分数相等,那么两个分数的排名应该相同。
在排名相同的分数后,排名数应该是下一个连续的整数。换句话说,排名之间不应该有空缺的数字。
按 score 降序返回结果表。
https://leetcode.cn/problems/rank-scores/solution/tu-jie-sqlmian-shi-ti-jing-dian-pai-ming-wen-ti-by/
# Write your MySQL query statement below
SELECT Score,
DENSE_RANK() OVER(ORDER BY Score DESC) AS 'Rank'
FROM Scores
180.连续出现的数字
https://leetcode.cn/problems/consecutive-numbers/solution/lian-xu-chu-xian-de-shu-zi-by-leetcode/
编写一个 SQL 查询,查找所有至少连续出现三次的数字。
# Write your MySQL query statement below
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
AND l1.Num = l2.Num
AND l2.Num = l3.Num
;
181.超过经理收入的员工
编写一个SQL查询来查找收入比经理高的员工。
https://leetcode.cn/problems/employees-earning-more-than-their-managers/solution/chao-guo-jing-li-shou-ru-de-yuan-gong-by-leetcode/
# Write your MySQL query statement below
SELECT
a.NAME AS Employee
FROM Employee AS a JOIN Employee AS b
ON a.ManagerId = b.Id
AND a.Salary > b.Salary
;
182.查找重复的电子邮箱
编写一个 SQL 查询,查找 Person 表中所有重复的电子邮箱。
https://leetcode.cn/problems/duplicate-emails/solution/cha-zhao-zhong-fu-de-dian-zi-you-xiang-by-leetcode/
# Write your MySQL query statement below
select Email from
(
select Email, count(Email) as num
from Person
group by Email
) as statistic
where num > 1
;
183.从不订购的客户
某网站包含两个表,Customers 表和 Orders 表。编写一个 SQL 查询,找出所有从不订购任何东西的客户。
https://leetcode.cn/problems/customers-who-never-order/solution/cong-bu-ding-gou-de-ke-hu-by-leetcode/
# Write your MySQL query statement below
select customers.name as 'Customers'
from customers
where customers.id not in
(
select customerid from orders
);
184.部门工资最高的员工
编写SQL查询以查找每个部门中薪资最高的员工。
https://leetcode.cn/problems/department-highest-salary/solution/bu-men-gong-zi-zui-gao-de-yuan-gong-by-leetcode/
# Write your MySQL query statement below
SELECT
Department.name AS 'Department',
Employee.name AS 'Employee',
Salary
FROM
Employee
JOIN
Department ON Employee.DepartmentId = Department.Id
WHERE
(Employee.DepartmentId , Salary) IN
( SELECT
DepartmentId, MAX(Salary)
FROM
Employee
GROUP BY DepartmentId
)
;
185.部门工资前三高的所有员工
公司的主管们感兴趣的是公司每个部门中谁赚的钱最多。一个部门的 高收入者 是指一个员工的工资在该部门的 不同 工资中 排名前三 。
编写一个SQL查询,找出每个部门中 收入高的员工 。
https://leetcode.cn/problems/department-top-three-salaries/solution/bu-men-gong-zi-qian-san-gao-de-yuan-gong-by-leetco/
# Write your MySQL query statement below
SELECT
d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
FROM
Employee e1
JOIN
Department d ON e1.DepartmentId = d.Id
WHERE
3 > (SELECT
COUNT(DISTINCT e2.Salary)
FROM
Employee e2
WHERE
e2.Salary > e1.Salary
AND e1.DepartmentId = e2.DepartmentId
)
;
196.删除重复的电子邮箱
编写一个 SQL 删除语句来 删除 所有重复的电子邮件,只保留一个id最小的唯一电子邮件。
https://leetcode.cn/problems/delete-duplicate-emails/solution/shan-chu-zhong-fu-de-dian-zi-you-xiang-by-leetcode/
# Please write a DELETE statement and DO NOT write a SELECT statement.
# Write your MySQL query statement below
DELETE p1 FROM Person p1,
Person p2
WHERE
p1.Email = p2.Email AND p1.Id > p2.Id
197.上升的温度
编写一个 SQL 查询,来查找与之前(昨天的)日期相比温度更高的所有日期的 id 。
https://leetcode.cn/problems/rising-temperature/solution/shang-sheng-de-wen-du-by-leetcode/
# Write your MySQL query statement below
SELECT w2.Id
FROM Weather w1, Weather w2
WHERE DATEDIFF(w2.RecordDate, w1.RecordDate) = 1
AND w1.Temperature < w2.Temperature