英文:
I want to understand how to make another sql query
问题
以下是使用窗口函数和公共表表达式（CTE）来解决问题的SQL查询：
WITH RankedSalaries AS (
SELECT
d.DepartmentName AS Department,
s.Name AS Employee,
s.Salary,
DENSE_RANK() OVER(PARTITION BY s.DepartmentId ORDER BY s.Salary DESC) AS SalaryRank
FROM Salary s
JOIN Department d ON s.DepartmentId = d.DepartmentId
)
SELECT Department, Employee, Salary
FROM RankedSalaries
WHERE SalaryRank <= 3;
这个查询首先使用CTE RankedSalaries 对每个部门中的员工工资按降序排名，并为每个员工工资计算密集排名。然后，它从排名结果中选择排名在前三位的员工，以找到每个部门中薪水最高的三名员工。
英文:
The database has a Salary table in which each employee has an ID, as well as a column for the department ID.
Write an SQL query to find the employees who receive the three highest salaries in each department.
Salary:
Id Name Salary DepartmentId
+----+-------+--------+----
| 1 | Petr | 85000 | 1 |
| 2 | Ivan | 80000 | 2 |
| 3 | Alex | 60000 | 2 |
| 4 | Den | 90000 | 1 |
| 5 | Bob | 69000 | 1 |
| 6 | Kir | 85000 | 1 |
| 7 | Mike | 76000 | 1 |
Department:
(DepartmentId, DepartmentName)
1 IT
2 Sales
The SQL query should return the following rows (the order of the rows does not matter):
Department Employee Salary
+-----------+----------+-----------+
| IT | Den | 90000 |
| IT | Petr | 85000 |
| IT | Kir | 85000 |
| IT | Mike | 76000 |
| Sales | Ivan | 80000 |
| Sales | Alex | 60000 |
here is my query:
SELECT d.name as Department,s1.name as Employee,s1.salary
FROM salary s1
left join department d on s1.departmentid=d.id
WHERE (SELECT COUNT(DISTINCT s2.salary)
from Department d2
WHERE s2.salary>s1.salary and s2.departmentid=s1.departmentid)<3
ORDER BY d.name,s1.salary DESC;
would like to solve with a window function and CTE
答案1
得分: 1
dense_rank()返回结果集分区中每一行的排名，排名值之间没有间隙。
dense_rank()给重复的行分配相同的排名（例如，工资为85000的两行都将有排名2）。
rn <= 3 通过此条件，我们控制应取多少个最高工资数字：
with cte as (
select *, dense_rank() over (partition by DepartmentId order by Salary desc) as rn
from Salary
)
select d.DepartmentName as Departement, Name as employee, Salary
from cte c
inner join Department d on d.DepartmentId = c.DepartmentId
where rn <= 3
order by c.departmentid, rn
英文:
dense_rank() returns the rank of each row within a result set partition, with no gaps in the ranking values.
dense_rank() give the same rank to duplicated rows (exemple salary 85000 both will have order 2)
rn <= 3 with this condition we control how many number of highest salaries should be taken :
with cte as (
select *, dense_rank() over (partition by DepartmentId order by Salary desc) as rn
from Salary
)
select d.DepartmentName as Departement, Name as employee, Salary
from cte c
inner join Department d on d.DepartmentId = c.DepartmentId
where rn <= 3
order by c.departmentid, rn
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论