SQL查询-尝试返回包含以前记录和当前记录的结果的记录

Question

所以我的表(表A(包含显示员工向谁报告的记录。当一个人更改"报告对象"时，它将更新表格并显示OLD和NEW值。我正在努力编写一个SQL查询，以返回前雇主和新雇主。

表A

-----------------------------------------------------------------------  
ID    |Date                           |Employee |Reports To  |Command
-----------------------------------------------------------------------   
1     |2020-05-05 09:32:56.193        |EMP0        |EMP1      OLD
-----------------------------------------------------------------------   
2     |2020-05-05 09:39:56.193        |EMP0        |EMP2      NEW
-----------------------------------------------------------------------   
3     |2020-05-05 11:32:56.193        |EMP0        |EMP2      OLD
-----------------------------------------------------------------------   
4     |2020-05-05 11:38:56.193        |EMP0        |EMP3      NEW
-----------------------------------------------------------------------   
5     |2020-05-05 19:32:56.193        |EMP0        |EMP3      OLD
-----------------------------------------------------------------------   
6     |2020-05-05 19:35:56.193        |EMP0        |EMP2      NEW
------------------------------------------------------------------------   

我想返回的结果：

------------------------------------------------------------   
ID    |Employee |Currently Reports To |Previously Reported To
-------------------------------------------------------------  
1      EMP0         EMP2               EMP1  
-------------------------------------------------------------  
2      EMP0         EMP3               EMP2      
-------------------------------------------------------------
3      EMP0         EMP2               EMP3      
------------------------------------------------------------- 

我尝试过的SQL查询：

SELECT Employee, tb1.[Reports To] FROM Table A tb1 JOIN Table A tb2
ON tb1.Employee =tb2.Employee AND tb1.Reports To <> tb2.Reports To
AND tb2.Command = 'OLD'
WHERE tb1.Command = 'NEW'```

Answer 1

如果"旧"one_answers"新"记录正确交错，您可以只使用lead()或lag():

select
employee,
reports_to     as currently_reports_to,
lag_reports_to as previously_reported_to
from (
select t.*, lag(reports_to) over(partition by employee order by date, id) lag_reports_to
from mytable t
) t
where command = 'NEW'

Answer 2

您也可以使用apply:

select row_number() over (order by t.date) as id,
t.employee, t.reportsto as CurrentlyReportsTo, t1.reportsto as PreviouslyReportsTo
from table t cross apply
( select top (1) t1.reportsto
from tab;e t1
where t1.employee = t.employee and t1.command = 'OLD' and t1.date <= t.date
order by t1.date desc
) t1
where t.command = 'NEW';

Answer 3

您的行具有完全相同的时间戳。这也允许您使用聚合：

select employee,
max(case when command = 'NEW' then reports_to end) as current_reports_to,
max(case when command = 'OLD' then reports_to end) as previously_reports_to
from t
group by employee, timestamp;