有趣的SQL难题
|
没有循环或游标，如何获取日期间隔列表并将其转换为1和0的字符串，使得：
每一位代表从最小（所有日期）到最大（所有日期）的每一天
如果该日期属于任何日期间隔，则该位为1
如果该日期不属于任何间隔，则该位为0
因此，例如，如果间隔为：
2011年1月1日至2011年1月2日
2011年1月4日至2011年1月5日
然后，您编写的SQL将输出11011。这是您可以使用的安装脚本：
declare @TimeSpan table
(
start datetime
,finish datetime
)
-- this is a good data set, with overlapping and non-overlapping time spans
insert into @TimeSpan values (\'02/02/2010\', \'02/02/2010\')
insert into @TimeSpan values (\'02/03/2010\', \'02/03/2010\')
insert into @TimeSpan values (\'02/04/2010\', \'02/05/2010\')
insert into @TimeSpan values (\'02/05/2010\', \'02/06/2010\')
insert into @TimeSpan values (\'02/07/2010\', \'02/09/2010\')
insert into @TimeSpan values (\'02/08/2010\', \'02/08/2010\')
insert into @TimeSpan values (\'02/08/2010\', \'02/10/2010\')
insert into @TimeSpan values (\'02/14/2010\', \'02/16/2010\')
-- for this set of data, the output string would be 111111111000111
没有找到相关结果
已邀请:
3 个回复
瞧叮
DECLARE @Result VARCHAR(MAX), @start DATETIME SELECT @start= MIN(start) , @Result =REPLICATE(\'0\',1+DATEDIFF(DAY,MIN(start),MAX(finish))) FROM @TimeSpan SELECT @Result = STUFF(@Result, DATEDIFF(DAY,@start,start)+1, DATEDIFF(DAY,start,finish)+1, REPLICATE(\'1\',1+DATEDIFF(DAY,start,finish))) FROM @TimeSpan SELECT @Result脖呐
DECLARE @BitString varchar(100); Declare @minStart datetime DECLARE @MaxEnd datetime declare @RangeDates table ( start datetime ,finish datetime ) -- this is a good data set, with overlapping and non-overlapping time spans insert into @RangeDates values (\'02/02/2010\', \'02/02/2010\') insert into @RangeDates values (\'02/03/2010\', \'02/03/2010\') insert into @RangeDates values (\'02/04/2010\', \'02/05/2010\') insert into @RangeDates values (\'02/05/2010\', \'02/06/2010\') insert into @RangeDates values (\'02/07/2010\', \'02/09/2010\') insert into @RangeDates values (\'02/08/2010\', \'02/08/2010\') insert into @RangeDates values (\'02/08/2010\', \'02/10/2010\') insert into @RangeDates values (\'02/14/2010\', \'02/16/2010\') SELECT @minStart = MIN(start) FROM @RangeDates SELECT @MaxEnd = MAX(finish) FROM @RangeDates ;WITH Dates AS ( SELECT myDate = CONVERT(DateTime, @minStart), CASE WHEN exists (SELECT * FROM @RangeDates where @minStart between start and finish) then \'1\' else \'0\' END as myBit UNION ALL SELECT myDate = DATEADD(DAY,1,myDate), CASE WHEN exists (SELECT * FROM @RangeDates where myDate between start and finish) then \'1\' else \'0\' END FROM Dates where myDate <= @MaxEnd ) SELECT @BitString = COALESCE(@BitString,\'\') + myBit FROM Dates SELECT @BitString眠缝
declare @Tally table ( N int identity(1,1), T bit ) insert into @Tally select TOP 11000 0 as T from master.dbo.SysColumns sc1, master.dbo.SysColumns sc2 declare @begin datetime = (select MIN(start) from @TimeSpan); declare @end datetime = (select MAX(finish) from @TimeSpan); with strings as ( select S.* , \'1\'+ REPLICATE(\'0\', DATEDIFF(DAY, @begin, DATEADD(DAY,N-1,S.start)))+ \'1\'+ REPLICATE(\'0\', DATEDIFF(DAY, DATEADD(DAY,N-1,S.start), @end)) task from @TimeSpan S inner join @Tally T ON DateAdd(DAY,T.N-1,S.start) <= S.finish ) select SUM(DISTINCT convert(numeric(38,0),task)) - COUNT(DISTINCT task)*(convert(numeric(38,0), \'1\' + REPLICATE(\'0\',DATEDIFF(d,@begin,@end)+1))) from strings