自行加入以在给定日期前一天计算

Question

我有这个表Employee_Hire其中包含部门中员工的雇用日期(ID(。现在我想写一个 Select 查询，再添加一个列 (Last_day( 以显示他在该部门工作的最后一天。例如，对于部门D01中的ID = 1，他的最后一天是2014/12/31。同样在D02中，他的最后一天是2017/12/31。

现在，由于他还在D04中工作，我们可以有一个像"直到现在"这样的字符串，或者像2099/12/31这样的未来日期或任何达到目的的字符串。

我想为表中的每个员工执行此操作。

+---+-------------+------------+
| Id| Dept_name   | Hiredate   |
+---+-------------+------------+
| 1 |      D01    | 2012-01-01 |
| 1 |      D02    | 2015-01-01 |
| 1 |      D03    | 2018-01-01 |
| 1 |      D04    | 2019-01-01 |
| 2 |      D01    | 2010-01-01 |
| 2 |      D02    | 2012-01-01 |
| 3 |      D01    | 2008-01-01 |
| 3 |      D02    | 2010-01-01 |
| 3 |      D03    | 2012-01-01 |
| 4 |      D01    | 2015-01-01 |
| 4 |      D02    | 2017-01-01 |
+---+-------------+------------+

我知道这可以使用 LEAD 或 LAG 功能来完成，但任何人都可以告诉我如何使用自加入来做到这一点吗？

提前感谢！！

Answer 1

假设 ROW_NUMBER(( 函数也不是禁区，此查询使用实际的自连接：

SELECT
curr.Id,
curr.Dept_name,
curr.Hiredate,
DATEADD(day, -1, next.Hiredate) AS Last_day
FROM
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY HireDate) AS RowNum
FROM EmployeeHire
) AS curr
LEFT OUTER JOIN
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY HireDate) AS RowNum
FROM EmployeeHire
) AS next
ON curr.ID = next.ID AND curr.RowNum = next.RowNum - 1

我不确定在没有 ROW_NUMBER(( 函数的情况下按任意条件排序时如何进行自连接。

Answer 2

您可以使用lead()和日期算法：

select
t.*,
dateadd(
day, 
-1, 
lead(hiredate, 1, '2100-01-01') over(partition by id order by hiredate)
) last_day
from mytable t

要lead()的第二个参数是偏移量(此处，您需要"下一行"，所以1(，第三个参数是默认值，为分区中的"最后一"行返回。

如果你想在没有窗口函数的情况下做到这一点，我会重新注释横向连接或子查询：

select t.*, x.last_day
from mytable t
cross apply (
select dateadd(
day, 
-1, 
coalesce(min(t1.hiredate), 2100-01-01')
) last_day
from mytable t1
where t1.id = t.id and t1.hiredate > t.hiredate
)

Answer 3

我认为通常我会选择窗口函数，但如果它不在表格中并且您的数据没有太多列，那么自连接与分组相结合看起来很干净：

select      a.id, a.Dept_name, a.Hiredate, 
last_day = dateadd(day, -1, min(b.Hiredate))
from        @hireData a
left join   @hireData b on a.Id = b.Id and b.Hiredate > a.Hiredate
group by    a.id, a.Dept_name, a.Hiredate