用于获取 SQL Server 2014 中每条记录的所有父项和子项的 SQL 查询

Question

>Emp表中的当前数据：

EmpId   Name         RejEmpId   DOJ             DOL         
----------------------------------------------------------
1       Name1           NULL    10-12-2014      12-06-2015  
2       Name1           1       06-04-2016      24-12-2016      
3       Name1           2       01-04-2017      NULL        
4       Name2           NULL    22-12-2014      21-07-2015  
5       Name2           4       10-04-2016      22-12-2016      
6       Name3           NULL    10-05-2015      NULL            
7       Name4           NULL    10-05-2015      NULL    

我想获取自定义列(所有父级和子级 EmpId、实际 DOJ、实际 DOL(

EmpId Name  RejEmpId DOJ        DOL         All             
Parent              
And
Child   Actual      Actual
EmpId   DOJ         DOL         
1    Name1  NULL    10-12-2014  12-06-2015  1.2.3   10-12-2014  NULL
2    Name1  1       06-04-2016  24-12-2016  1.2.3   10-12-2014  NULL
3    Name1  2       01-04-2017  NULL        1.2.3   10-12-2014  NULL
4    Name2  NULL    22-12-2014  21-07-2015  4.5     22-12-2014  22-12-2016
5    Name2  4       10-04-2016  22-12-2016  4.5     22-12-2014  22-12-2016
6    Name3  NULL    10-05-2015  NULL        6       10-05-2015  NULL
7    Name4  NULL    10-05-2015  NULL        7       10-05-2015  NULL
8    Name5  NULL    12-06-2015  20-12-2016  8       12-06-2015  20-12-2016

Answer 1

如果我理解正确，您可以按如下方式查询：

对于连接，您可以使用Stuff，获取DOJ的第一个值，您可以使用SQL Server 2012 +中的FIRST_VALUE函数，同样，对于DOL，您可以使用LAST_VALUE函数，如下所示：

Select emp.EmpId, Emp.[Name], RejEmpId, emp.DOJ, DOL, concatStr as [AllParent and Child EmpId]
,FIRST_VALUE(DOJ) over(partition by emp.[Name] order by EmpId) as ActualDOJ
,LAST_VALUE(DOL) over(partition by emp.[Name] order by EmpId rows between current row and unbounded following) as AcualDOL
from #empdata emp
cross apply (
Select [Name], concatStr = stuff((select concat('.', EmpId) from #empdata e where e.[Name] = o.[Name] for xml path('')),1,1,'')  
from #empdata o
group by [Name]
) c where emp.[Name] = c.[Name]

输出如下：

+-------+-------+----------+------------+------------+---------------------------+------------+------------+
| EmpId | Name  | RejEmpId |    DOJ     |    DOL     | AllParent and Child EmpId | ActualDOJ  |  AcualDOL  |
+-------+-------+----------+------------+------------+---------------------------+------------+------------+
|     1 | Name1 | NULL     | 2014-12-10 | 2015-06-12 |                     1.2.3 | 2014-12-10 | NULL       |
|     2 | Name1 | 1        | 2016-04-06 | 2016-12-24 |                     1.2.3 | 2014-12-10 | NULL       |
|     3 | Name1 | 2        | 2017-04-01 | NULL       |                     1.2.3 | 2014-12-10 | NULL       |
|     4 | Name2 | NULL     | 2014-12-22 | 2015-07-21 |                       4.5 | 2014-12-22 | 2016-12-22 |
|     5 | Name2 | 4        | 2016-04-10 | 2016-12-22 |                       4.5 | 2014-12-22 | 2016-12-22 |
|     6 | Name3 | NULL     | 2015-05-10 | NULL       |                         6 | 2015-05-10 | NULL       |
|     7 | Name4 | NULL     | 2015-05-10 | NULL       |                         7 | 2015-05-10 | NULL       |
+-------+-------+----------+------------+------------+---------------------------+------------+------------+