SQL中的父子层次结构 [英] Parent Child Hierarchy in SQL
本文介绍了SQL中的父子层次结构的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有以下数据
I have the following data
Row ID Start_Date End_Date Group
1 0123456789 2003-01-01 2003-08-20 60002
2 0123456789 2003-08-20 2003-11-07 60002
3 0123456789 2003-11-07 2003-12-11 60002
4 0123456789 2003-12-11 2004-03-05 60002
5 0123456789 2004-03-05 2004-06-29 60002
6 0123456789 2004-06-29 2005-01-01 60002
7 0123456789 2005-01-01 2005-05-24 83002
8 0123456789 2005-05-24 2005-05-25 83002
9 0123456789 2005-05-25 2005-10-20 83002
10 0123456789 2005-10-20 2006-03-08 83002
11 0123456789 2006-03-08 2006-05-03 83002
12 0123456789 2006-05-03 2006-05-31 83002
13 0123456789 2006-05-31 2006-11-06 83002
14 0123456789 2006-11-06 2006-12-01 83002
15 0123456789 2006-12-01 2007-03-20 733002
16 0123456789 2007-03-20 2007-05-25 733002
17 0123456789 2007-05-25 2007-09-19 733002
18 0123456789 2007-09-19 2007-12-10 733002
19 0123456789 2007-12-10 2008-01-01 733002
20 0123456789 2008-01-01 2008-09-30 60002
21 0123456789 2008-09-30 2008-12-24 60002
22 0123456789 2008-12-24 2009-06-11 60002
23 0123456789 2009-06-11 2009-06-16 60002
24 0123456789 2009-06-16 2009-11-26 60002
25 0123456789 2009-11-26 2010-10-12 60002
26 0123456789 2010-10-12 2011-07-14 60002
27 0123456789 2011-07-14 2011-09-07 60002
28 0123456789 2011-09-07 2011-10-07 60002
29 0123456789 2011-10-07 2011-11-15 60002
30 0123456789 2011-11-15 2012-03-30 60002
31 0123456789 2012-03-30 2012-04-05 60002
32 0123456789 2012-04-05 2012-05-04 60002
33 0123456789 2012-05-04 2012-08-22 60002
34 0123456789 2012-08-22 NULL 60002
我需要的是
What I need is
ID Start_Date End_Date Group
0123456789 2003-01-01 2005-01-01 60002
0123456789 2005-01-01 2006-12-01 83002
0123456789 2006-12-01 2008-01-01 733002
0123456789 2008-01-01 NULL 60002
逻辑：第一行End_Date需要匹配第二个Start_Date，wher e组是相同的，依此类推。
如果日期中断，
即第26行被删除，它将需要返回
Logic: 1st row End_Date needs to match 2nd Start_Date, where Group is the same, and so on.
If there is a break in the dates,
ie Row 26 was removed, it would need to return
ID Start_Date End_Date Group
0123456789 2003-01-01 2005-01-01 60002
0123456789 2005-01-01 2006-12-01 83002
0123456789 2006-12-01 2008-01-01 733002
0123456789 2008-01-01 2010-10-12 60002
0123456789 2011-07-14 NULL 60002
如何有效地在MS SQL中实现这一点，因为这样可以节省后期处理。
How could this be achieve in MS SQL efficiently, as this would save post processing.
推荐答案
这样......
请用你的tablename替换带下划线的部分
this way...
please replace underlined part with your tablename
with t as
(
select id,start_date, end_date,g,g1 from
(
select a.id,a.start_date,a.end_date,a.[group] as g,aa.[group] as g1
from TABEL_NAME as a
left join TABEL_NAME as aa on a.start_date = aa.End_Date
) as a
where g<>g1 or g1 is null
)
select distinct t.id,t.start_date,case when t.start_date > tt.Start_date then null else tt.start_date end as end_date,t.g as [Group]
from t
Left join t as tt on t.g = tt.g1
只是为了测试...
just to test...
with a as
(
select 1 as Row, '0123456789' as ID, '2003-01-01' as Start_Date, '2003-08-20' as End_Date, 60002 as [Group]
union all
select 2 ,'0123456789', '2003-08-20','2003-11-07' ,60002 union all
select 3 ,'0123456789', '2003-11-07','2003-12-11' ,60002 union all
select 4 ,'0123456789', '2003-12-11','2004-03-05' ,60002 union all
select 5 ,'0123456789', '2004-03-05','2004-06-29' ,60002 union all
select 6 ,'0123456789', '2004-06-29','2005-01-01' ,60002 union all
select 7 ,'0123456789', '2005-01-01','2005-05-24' ,83002 union all
select 8 ,'0123456789', '2005-05-24','2005-05-25' ,83002 union all
select 9 ,'0123456789', '2005-05-25','2005-10-20' ,83002 union all
select 10 ,'0123456789', '2005-10-20','2006-03-08' ,83002 union all
select 11 ,'0123456789', '2006-03-08','2006-05-03' ,83002 union all
select 12 ,'0123456789', '2006-05-03','2006-05-31' ,83002 union all
select 13 ,'0123456789', '2006-05-31','2006-11-06' ,83002 union all
select 14 ,'0123456789', '2006-11-06','2006-12-01' ,83002 union all
select 15 ,'0123456789', '2006-12-01','2007-03-20' ,733002 union all
select 16 ,'0123456789', '2007-03-20','2007-05-25' ,733002 union all
select 17 ,'0123456789', '2007-05-25','2007-09-19' ,733002 union all
select 18 ,'0123456789', '2007-09-19','2007-12-10' ,733002 union all
select 19 ,'0123456789', '2007-12-10','2008-01-01' ,733002 union all
select 20 ,'0123456789', '2008-01-01','2008-09-30' ,60002 union all
select 21 ,'0123456789', '2008-09-30','2008-12-24' ,60002 union all
select 22 ,'0123456789', '2008-12-24','2009-06-11' ,60002 union all
select 23 ,'0123456789', '2009-06-11','2009-06-16' ,60002 union all
select 24 ,'0123456789', '2009-06-16','2009-11-26' ,60002 union all
select 25 ,'0123456789', '2009-11-26','2010-10-12' ,60002 union all
select 26 ,'0123456789', '2010-10-12','2011-07-14' ,60002 union all
select 27 ,'0123456789', '2011-07-14','2011-09-07' ,60002 union all
select 28 ,'0123456789', '2011-09-07','2011-10-07', 60002 union all
select 29 ,'0123456789', '2011-10-07','2011-11-15', 60002 union all
select 30 ,'0123456789', '2011-11-15','2012-03-30', 60002 union all
select 31 ,'0123456789', '2012-03-30','2012-04-05', 60002 union all
select 32 ,'0123456789', '2012-04-05','2012-05-04', 60002 union all
select 33 ,'0123456789', '2012-05-04','2012-08-22', 60002 union all
select 34 ,'0123456789', '2012-08-22', NULL , 60002
)
select distinct t.id,t.start_date,case when t.start_date > tt.Start_date then null else tt.start_date end as end_date,t.g as [Group] from
(
select id,start_date, end_date,g,g1 from
(
select a.id,a.start_date,a.end_date,a.[group] as g,aa.[group] as g1 from a
left join a as aa on a.start_date=aa.End_Date
) as a
where g<>g1 or g1 is null
) as t
left join
(
select id,start_date,end_date,g,g1 from
(
select a.id,a.start_date,a.end_date,a.[group] as g,aa.[group] as g1 from a
left join a as aa on a.start_date=aa.End_Date
) as a
where g<>g1 or g1 is null
) as tt on t.g=tt.g1
快乐编码！
：）
Happy Coding!
:)
Hi,
Select ID,Min(Start_date) Start_date,Max(End_Date) End_Date,Group
From [Your Table Name]
Group BY ID,Group
Order By Start_date
您好，
我已更改您的专栏name Group to GroupId
试试这个
Hi,
I have change your column name Group to GroupId
Try This
Select FM.ID,FM.Start_Date,FM2.End_Date,FM.GroupId FROM
(
Select Row_Number() Over(Order By ID,GroupId,IsNull(Start_Date,End_Date)) RowNum,ID, Start_Date, End_Date ,GroupId
FROM (
Select ID, Case When Type='SD' Then Date End Start_Date,
Case When Type='ED' Then Date End End_Date,
GroupId
From (
Select ID,Date,GroupId,Type
From (
Select Distinct ID,Start_Date Date,GroupId,'SD' Type From [Table Name] Where Start_Date is Not Null
Union
Select ID,End_Date Date,GroupId,'ED' Type From [Table Name] Where End_Date is Not Null)
BM
inner join
(Select ID,Date,GroupId
From (
Select Distinct ID,Start_Date Date,GroupId,'SD' Type From [Table Name] Where Start_Date is Not Null
Union
Select ID,End_Date Date,GroupId,'ED' Type From [Table Name] Where End_Date is Not Null)
BST
Group By ID,Date,GroupId
Having(Count(*)=1)) BS On BM.ID=BS.ID And BM.Date=BS.Date And BS.GroupId=BM.GroupId
) IM
) AM
) FM
Left Join
(Select Row_Number() Over(Order By ID,GroupId,IsNull(Start_Date,End_Date)) RowNum,ID, Start_Date, End_Date ,GroupId
FROM (
Select ID, Case When Type='SD' Then Date End Start_Date,
Case When Type='ED' Then Date End End_Date,
GroupId
From (
Select ID,Date,GroupId,Type
From (
Select Distinct ID,Start_Date Date,GroupId,'SD' Type From [Table Name] Where Start_Date is Not Null
Union
Select ID,End_Date Date,GroupId,'ED' Type From [Table Name] Where End_Date is Not Null)
BM
inner join
(Select ID,Date,GroupId
From (
Select Distinct ID,Start_Date Date,GroupId,'SD' Type From [Table Name] Where Start_Date is Not Null
Union
Select ID,End_Date Date,GroupId,'ED' Type From [Table Name] Where End_Date is Not Null)
BST
Group By ID,Date,GroupId
Having(Count(*)=1)) BS On BM.ID=BS.ID And BM.Date=BS.Date And BS.GroupId=BM.GroupId
) IM
) BM ) FM2 ON FM.RowNum+1=FM2.RowNum and FM.Start_Date is Not Null
Union
Select Distinct ID,Start_Date,End_Date,GroupId From [Table Name] Where End_Date is Null这篇关于SQL中的父子层次结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
