ROW_NUMBER() & RANK() - 第二高的叶数 - SQL Server

发布于 2025-01-17 06:09:33 字数 833 浏览 2 评论 0原文

https://i.sstatic.net/FH0Z8.png

我需要帮助来编写 SQL查询打印请假第二多的员工的姓名。

因此，我需要使用 ROW_NUMBER() 和 RANK()< 从 HumanResources.Employee 表中获取第二高的休假数（休假 + 病假） /code> SQL Server Adventureworks2019 示例数据库中。

感谢您抽出时间。

SQL查询：

SELECT 
    S.BusinessEntityID, LEAVES,
    ROW_NUMBER() OVER (PARTITION BY LEAVES  ORDER BY LEAVES) AS RN
FROM
    (SELECT 
         BusinessEntityID, 
         (VacationHours + SickLeaveHours) AS LEAVES
     FROM      
         [HumanResources].[Employee]) S
GROUP BY 
    LEAVES, S.BusinessEntityID 
ORDER BY 
    LEAVES DESC

https://i.sstatic.net/FH0Z8.png

原文

https://i.sstatic.net/FH0Z8.png

I need help to write a SQL query to print the names of the employees that took the second-highest leaves.

So I need to get the 2nd highest no of leaves (vacation leaves + sick leaves) from the HumanResources.Employee table using ROW_NUMBER() and RANK() in the SQL Server Adventureworks2019 sample database.

Thanks for your time.

SQL query:

SELECT 
    S.BusinessEntityID, LEAVES,
    ROW_NUMBER() OVER (PARTITION BY LEAVES  ORDER BY LEAVES) AS RN
FROM
    (SELECT 
         BusinessEntityID, 
         (VacationHours + SickLeaveHours) AS LEAVES
     FROM      
         [HumanResources].[Employee]) S
GROUP BY 
    LEAVES, S.BusinessEntityID 
ORDER BY 
    LEAVES DESC

https://i.sstatic.net/FH0Z8.png

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

如梦初醒的夏天 2025-01-24 06:09:33

第二高...

select * from (
SELECT 
   S.BusinessEntityID, 
   LEAVES,
   ROW_NUMBER() OVER(PARTITION BY LEAVES  ORDER BY LEAVES) AS RN,
   DENSE_RANK() OVER(ORDER By LEAVES DESC) as DR
FROM(
      SELECT 
         BusinessEntityID, 
         (VacationHours+SickLeaveHours) AS LEAVES
      FROM [HumanResources].[Employee]      
    ) S
GROUP BY LEAVES, S.BusinessEntityID 
) x where x.DR=2

输出：

BusinessEntityID	LEAVES	RN	DR
116	167	1	2
124	167	2	2
92	167	3	2

Second highest...

select * from (
SELECT 
   S.BusinessEntityID, 
   LEAVES,
   ROW_NUMBER() OVER(PARTITION BY LEAVES  ORDER BY LEAVES) AS RN,
   DENSE_RANK() OVER(ORDER By LEAVES DESC) as DR
FROM(
      SELECT 
         BusinessEntityID, 
         (VacationHours+SickLeaveHours) AS LEAVES
      FROM [HumanResources].[Employee]      
    ) S
GROUP BY LEAVES, S.BusinessEntityID 
) x where x.DR=2

output: