这样......

请用你的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