declare @T table (ItemId int, IntervalID int, StartDate datetime, EndDate datetime)insert into @Tselect 1, 1, NULL, '2011-01-15' union allselect 2, 1, '2011-01-16', '2011-01-25' union allselect 3, 1, '2011-01-26', NULL union allselect 4, 2, NULL, '2011-01-17' union allselect 5, 2, '2011-01-16', '2011-01-25' union allselect 6, 2, '2011-01-26', NULLselect T1.*from @T as T1 inner join @T as T2 on coalesce(T1.StartDate, '1753-01-01') < coalesce(T2.EndDate, '9999-12-31') and coalesce(T1.EndDate, '9999-12-31') > coalesce(T2.StartDate, '1753-01-01') and T1.IntervalID = T2.IntervalID and T1.ItemId <> T2.ItemId
结果：
ItemId IntervalID StartDate EndDate----------- ----------- ----------------------- -----------------------522011-01-16 00:00:00.000 2011-01-25 00:00:00.00042NULL 2011-01-17 00:00:00.000
