求不连续的数据

[liujuns]

DECLARE @calendar_date TABLE (Item int ,calendardate SMALLDATETIME)
INSERT INTO @calendar_date
SELECT 1 ,'2006-01-01 00:00:00'
UNION
SELECT 1, '2007-01-01 00:00:00'
UNION
SELECT 1 ,'2009-01-01 00:00:00'
UNION
SELECT 2, '2006-01-01 00:00:00'
UNION
SELECT 2, '2007-01-01 00:00:00'
UNION
SELECT 2, '2008-01-01 00:00:00'



结果是:
输入 : Begin: '2006-01-01 00:00:00' END '2009-01-01 00:00:00'

SELECT 1 ,'2008-01-01 00:00:00' ,'缺'
UNION
SELECT 2,'2009-01-01 00:00:00' ,'缺'

[黄_瓜]

DECLARE @calendar_date TABLE (Item int ,calendardate SMALLDATETIME)
INSERT INTO @calendar_date  
SELECT 1 ,'2006-01-01 00:00:00'
UNION  
SELECT 1, '2007-01-01 00:00:00'
UNION  
SELECT 1 ,'2009-01-01 00:00:00'  
UNION
SELECT 2, '2006-01-01 00:00:00'
UNION  
SELECT 2, '2007-01-01 00:00:00'
UNION  
SELECT 2, '2008-01-01 00:00:00' 


declare @Begin datetime,@end datetime
select @Begin='2006-01-01 00:00:00', @end= '2009-01-01 00:00:00'

select Item,dateadd(yy,1,calendardate) as start,'缺'

from @calendar_date as t   

where not exists  
(
select 1 from @calendar_date where calendardate=dateadd(yy,1,t.calendardate) 
and Item=t.Item  
and  t.calendardate > @Begin 
and  t.calendardate <@end 
)
and  t.calendardate > @Begin and  t.calendardate <@end 
/*
Item        start                                                       
----------- ------------------------------------------------------ ---- 
1           2008-01-01 00:00:00                                    缺
2           2009-01-01 00:00:00                                    缺

（所影响的行数为 2 行）


*/

[claro]

看不懂

帮顶