1、部门最高工资
select a.departmentid,a.salary from Employee a left JOIN employee b on a.DepartmentId=b.DepartmentId and a.Salary<b.Salary GROUP BY a.salary ,a.departmentid HAVING count(b.Salary)=0
#1、表连接的意义在于A表与B表所有人工资进行一个比较
#2、左连接的意义在于当工资最高时候用内连接,会被过滤掉,左连接却可以返回
#3、整个SQL意思是同个部门每个员工都与自己部门所有人比较一轮,一个员工即为一轮,当某一轮a.Salary<b.Salary 的数量即count(b.Salary)=0时,代表部门没人比你工资高,那么你就是部门最高工资,
最终结果为
departmentid salary
2 80000
1 90000
2、部门前三工资、3、各部门前三工资
select a.departmentid,a.salary from Employee a left JOIN employee b on a.DepartmentId=b.DepartmentId and a.Salary<b.Salary GROUP BY a.salary ,a.departmentid HAVING count(DISTINCT b.Salary)<3
#1、count(DISTINCT b.Salary)=0时候找出最高工资,count(DISTINCT b.Salary)=1则找出第二高工资,count(DISTINCT b.Salary)=2找出第三高工资,count(DISTINCT b.Salary)<3则找出前三高的工资
#2、count(DISTIN