LeetCode-SQL-180-185

180-连续出现的数字

#题目要求：*连续*出现三次的数字
select distinct l1.num as ConsecutiveNums   #保证num的输出为不重复的
from
    Logs l1,
    Logs l2,
    Logs l3
WHERE
    l1.Id = l2.Id - 1           #三个id连续
    And l2.Id = l3.Id-1
    And l1.Num = l2.Num         #三个num相等
    And l2.Num = l3.Num

181-超过经理收入的员工
以下两种格式都可以

select e1.name as Employee
from Employee e1
where e1.salary > (select
    e2.salary
    from Employee e2
    where id = e1.managerId
)

select e1.name as Employee
from 
Employee e1,Employee e2
where
e1.salary > e2.salary and e2.id = e1.managerId

182-查找重复的电子邮箱

#按照email分组来去重
select email as Email
from Person
group by email
having count(email) > 1

#按照邮箱相同，但是id不同的方式进行查询
select
	distinct p1.email as Email
from 
	Person p1, Person p2
where
	p1.email = p2.email and p1.id != p2.id

183-从不订购的用户
同样是两种方法

select Name as Customers
from Customers
#查询条件是在order表中没有出现的id
where Id not in
    (select CustomerId
     from Orders
     where Orders.CustomerId = Customers.Id)

#查询条件是通过左连接的方式，找到null的进行排除，保留非null字段
select Name Customers
from Customers
left join Orders
on Orders.CustomerId = Customers.Id
where Orders.Id is null

184-部门工资最高的员工

SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM
    Employee JOIN Department 
    ON Employee.DepartmentId = Department.Id
#以上是将表联结起来，并选好字段
#以下是查询的一个条件————按照部门，找工资最高
where
	(departmentId,salary)
in
	(select departmentId, max(salary)
	 from Employee
	 group by departmentId
	)

185- 部门工资前三高的所有员工
前三：指的是，比他高的人不超过3个

#一样的思路，先联结 选好字段之后，再进行筛选(找前几的人，可以通过having来筛)
select d.name as Department, e.name as Employee, e.salary as Salary
from Employee e
join Department d on e.departmentId = d.id
where
	e.id
in
	( #找每个部门的工资前三名，那么先在子查询中用Employee和自己做连接，连接条件是【部门相同但是工资比我高】
    select e1.id
    from Employee as e1 left join Employee as e2
    on e1.departmentId = e2.departmentId and e1.Salary < e2.Salary
	group by e1.id		#按员工id进行分组
	#如果【跟我一个部门而且 工资比我高 的 人数】不超过3个(e2是工资高的那个，还需要加distinct)
	having count(distinct e2.Salary) <=2
	)