磨刀霍霍:做题笔记-190624

刷题笔记-190624

第一题

182. 查找重复的电子邮箱

解法 1:

select t.Email from Person t 
group by t.Email
having count(t.Email) > 1

知识点:

  • HAVING 的使用方法

第二题

595. 大的国家

解法 1:

select name, population, area from World
where 1=1
and (population > 25000000 or area > 3000000)

解法 2:

select name, population, area from World
group by area
having area > 3000000
union
select name, population, area from World
group by population
having population > 25000000

知识点:

  • UNION 的使用
  • 逻辑判断的先后顺序

第三题

620. 有趣的电影

解法 1:

SELECT id,movie,description,rating FROM cinema
WHERE id % 2 =1
AND description <> 'boring'
ORDER BY rating DESC

知识点:

  • 不等于不建议使用 != 而是使用 <>

第四题

185. 部门工资前三高的员工

解法 1:

select 
t2.Name as Department,
t1.Name as Employee,
t1.Salary as Salary
from Employee t1
inner join Department t2 on t2.Id = t1.DepartmentId
where 3 > (select count(distinct Salary)
from Employee
where Salary > t1.Salary
and DepartmentId = t1.DepartmentId)