列：RecYear(int),RecMonth(int),RecDay(int),T0000,T0010,T0020,T0030,T0040..
将2012-5-12的数据与2012-5-11的数据组合
declare @sql as nvarchar(max)
declare @selectstr as nvarchar(max)
set @selectstr = 'a.RecYear,a.RecMonth,a.RecDay,'
declare @i as int
set @i=0
while @i<=50
begin
--print right('0000'+cast(@i as nvarchar(4)),4)
set @selectstr = @selectstr + 'a.T'+right('0000'+cast(@i as nvarchar(4)),4) + ','
set @i=@i+10
end
set @i=60
while @i<=90
begin
--print right('0000'+cast(@i as nvarchar(4)),4)
set @selectstr = @selectstr + 'b.T'+right('0000'+cast(@i as nvarchar(4)),4) + ','
set @i=@i+10
end
set @selectstr = left(@selectstr,len(@selectstr)-1)
set @sql = 'select ' + @selectstr + ' from License
a left join License b
on dbo.toDate(a.RecYear,a.RecMonth,a.RecDay)=DateAdd(dd,1,dbo.toDate(b.RecYear,b.RecMonth,b.RecDay))
'
exec(@sql)
create function toDate(@year int,@month int,@day int)
returns datetime
as
begin
return(select cast(cast(@year as varchar) +'-'+ cast(@month as varchar) +'-'+ cast(@day as varchar) as datetime))
end
