SQL显示最多2名员工雇用了每个部门2021年的最新员工

发布于 2025-02-09 15:59:44 字数 638 浏览 2 评论 0 原文

SQL Server数据库有2个表：

Department ：ID
员工：ID，部门，Employee_name，date_hired

我想显示最多2名员工雇用了最新的2021年，每个部门

例如

输出应为

我有这个SQL：

SELECT e.* 
FROM Employee e
WHERE e.Date_Hired IN (SELECT e.Date_Hired
                      FROM Employee e
                      WHERE YEAR(e.Date_Hired) = 2021)
ORDER BY e.Date_Hired DESC

但是它正在显示部门1中的所有3个项目

。

原文

A SQL Server database has 2 tables:

Department: Id
Employee: Id, Department, Employee_Name, Date_Hired

I wanted to display maximum 2 employees hired the latest in 2021 per department

For example

The output should be

I have this SQL:

SELECT e.* 
FROM Employee e
WHERE e.Date_Hired IN (SELECT e.Date_Hired
                      FROM Employee e
                      WHERE YEAR(e.Date_Hired) = 2021)
ORDER BY e.Date_Hired DESC

But it is displaying all 3 items from department 1.

Thanks in advance!

分享到QQ

分享到微博

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

发布评论

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

断爱 2025-02-16 15:59:46

SELECT e.*
FROM Employee e,
     (SELECT id,
             Date_Hired,
             row_number() OVER(PARTITION BY Department ORDER BY id DESC) AS rn
      FROM Employee e
      WHERE YEAR (e.Date_Hired) = 2021) sub
WHERE rn <= 2
  and e.id = sub.id
ORDER by e.Date_Hired DESC

SELECT e.*
FROM Employee e,
     (SELECT id,
             Date_Hired,
             row_number() OVER(PARTITION BY Department ORDER BY id DESC) AS rn
      FROM Employee e
      WHERE YEAR (e.Date_Hired) = 2021) sub
WHERE rn <= 2
  and e.id = sub.id
ORDER by e.Date_Hired DESC

回复收藏 0 原文