知识点汇总:
- 175:LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行,即使在右表 (table_name2) 中没有匹配的行。 右表没有记录的行会以NULL显示。
- 176: 临时表的性质, IFNULL
- 182: 查重可以用 group by XX having count(*)
- 184: 元组可以多个字段 in 比如 (DepartmentId , Salary) in (x, x)
175. 组合两个表
select FirstName, LastName, City, State from Person p left join Address ad on p.PersonId = ad.PersonId
知识点: LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行,即使在右表 (table_name2) 中没有匹配的行。 右表没有记录的行会以NULL显示。
176. 第二高的薪水
方法一:
select
(select distinct Salary from Employee e order by Salary desc limit 1,1) as SecondHighestSalary
方法二:
SELECT
IFNULL(
(SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1),
NULL) AS SecondHighestSalary
知识点:
第二高的薪水可能不存在,这个时候如果直接使用
select distinct Salary from Employee e order by Salary desc limit 1,1
会报错。
方法一是利用了临时表的性质,当第二高的薪水不存在时,外层再加一个select, 相当于是 select NULL
方法二则是利用了 IFNULL()方法来实现。
182. 查找重复的邮箱
select Email from Person group by Email having count(*) > 1
知识点: 查重可以用 group by XX having count(*)
184. 部门工资最高的员工
https://leetcode-cn.com/problems/department-highest-salary/
select Department.Name as Department, Employee.Name as Employee, Salary from Employee inner join Department on Employee.DepartmentId = Department.Id where
(DepartmentID, Salary) in (
select DepartmentID, max(Salary) from Employee group by DepartmentID)
思路: ① 找出每个部门对应的最高工资
② 从员工表中找属于该部门且工资 = 最高工资的员工
知识点: ① 元组也可以多个字段 in 比如 (DepartmentId , Salary) in (x, x)