Leetcode D3
181. 超过经理收入的员工(简单)
终于出现了我会的题目了!嗷嗷嗷!开心。虽然只是到简单的自回归题目,当然还是傻傻分不清楚到底怎么链接。我首先想到的是用where来使用92自链接查询:
select a.name as Employee
from employee a,employee b
where a.managerId = b.Id
and a.salary > b.salary;
当然也有另外一种查询方法,99查询规则,因为是内联,所以是join,不需要用left或者right:
select a.name as Emploee
from employee a
join employee b
on a.managerId = b.Id
and a.salary > b.salary
- 查找重复项(简单)
首先,如果想要查找重复项,可以通过group by分组,然后通过count计算每个组里面的个数,如果个数大于1,那么就是有重复项啦。
select email as 'Email'
from person
group by email
having count(email)>1;
当然看到了另外一种解法,是创捷临时表的做法,感觉就相当于是子查询叭。但是我在上传中的速度发现,下面一种方法要比上面一种更快一点哦。
select a.email as 'Email'
from (select email , count(email) num
from person
group by email
) a
where a.num >1;
- 从不订购的客户/查找不在表里的数据
这道题首先想到的就是外连接,并且可以采用99里面的左连接。今天看到了一个比较好理解的解释,左连接的左边是主表,所以保存左边的所有数据,然后右边的从表来匹配主表。我感觉好像是这个意思。那么如果customers为主表,那么order表里面有数据则匹配,然后没有数据则为null,所以就检查是不是为null,而null只能用is (not)来判断。
select c.name as 'Customers'
from Customers c
left join Orders o
on c.id=o.customerid
where o.customerid is null;
- 部门工资最高的员工
这道题首先想到了左连接,然后group by。结果发现错误了。首先如果是左连接,可能出现部门名称没有匹配的则为null了,可是部门名称不能为null。然后group by 不能选择出并列值,如果部门里面的最高工资有同时几个人获得的话,就不能用group by。然后看了解答,解答是用的内联,然后where in的方法来筛选的。
select d.name as 'Department',e.name as 'Employee',salary
from Employee e
join Department d
on e.departmentId = d.Id
where (e.departmentId , salary) in
(select departmentId,max(salary)
from Employee
group by departmentId);
首先where里面的筛选是组合筛选。筛选出了每个部门的部门Id和最高工资.然后通过部门Id和工资来匹配,名字不参与这个过程,这样就可以避免并列的问题。
- 部门工资前三高的员工
这道题,有两个解法,官方解法太灵性了,勉强能够理解。是首先选出前三工资,然后匹配姓名,其实和刚刚的题目很像,所以就是where后面筛选的条件有变化。但是他之后where后面筛选出了salary,在前面的条件里面在加上部门的不同。
筛选条件图解:
总体的代码如下:
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 3>(select count(distinct em.salary)
from employee em
where em.salary > e.salary
and e.departmentId = em.departmentId )
ORDER BY d.NAME,Salary DESC;
第二种解法就是,通过组内排序然后筛选前3。既要分组,还要排序,其实昨天的题目中有涉及主要是窗口函数的运用,并且在遇到这种问题应该准确的想到窗口函数的运用。
select departmentid as 'Department', name as 'Employee',salary as 'Salary'
from (
select * ,
dense_rank() over (partition by departmentid
order by salary DESC) as ranking
from employee) as a
where ranking < = 3;