sqlGirl

今夜520,我们做个sql people吧

一.第二高的薪水

https://leetcode-cn.com/problems/second-highest-salary/

SELECT(
    SELECT DISTINCT Salary 
    FROM Employee 
    GROUP BY Salary 
    ORDER BY Salary 
    DESC LIMIT 1,1
    )AS SecondHighestSalary

建立一个临时表就可以解决当表中工资全一样或者表中只有一个工资数据时可以返回null。

同样我相信如果要你求第n高的薪水,你也能求出来。思考下吧

二.分数排名

https://leetcode-cn.com/problems/rank-scores/

解题分两部分,第一部分分数降序排列;第二部分找到分数对应的排名。

第一部分不难,第二部分需要将分数去重,然后对大于等于要计算排名的分数个数求count(),就得到了排名数。对于例子中的3.65分数,表中分数大于等于3.65且不重复的有4.0,3.85,3.65,有三个,所以3.65分数排名为3。

SELECT a.Score ,(SELECT COUNT(Distinct b.Score) FROM Scores AS b Where b.Score >= a.Score ) AS 'Rank'
FROM Scores AS a
ORDER BY a.Score DESC;

三.超过经理工资的员工

https://leetcode-cn.com/problems/employees-earning-more-than-their-managers/submissions/

使用join连接a,b两个表,找出每一条数据中a.ManagerID等于b.ID并且a.Salary>b.Salary,即可求解。

SELECT a.Name AS Employee
FROM Employee AS a JOIN Employee AS b ON a.ManagerID = b.Id
AND a.Salary > b.Salary; 

四.部门工资最高的员工

https://leetcode-cn.com/problems/department-highest-salary/

SELECT Department.Name AS Department, 
       Employee.Name AS Employee,
       Salary
FROM Employee JOIN Department 
       on Employee.DepartmentId = Department.ID
WHERE (Employee.DepartmentId,Salary) IN(
       SELECT DepartmentId, MAX(Salary)
       FROM Employee
       GROUP BY DepartmentId 
)

第二个select语句是在员工表中查找每个部门中最高工资,然后此临时表再去跟部门表做join,找出部门名称。(为什么临时表不找出拿最高工资的雇员的名字?因为此临时表是要去与部门表连接求出部门名字的,临时表求出雇员名字没有意义)

今夜520,我们做个sql people吧已关闭评论