declare @countdate int
declare @Datetime varchar(20)
declare @i int
--设置日期为30天
set @countdate=30
set @Datetime='2008-09-08'
while @i<@countdate
begin
select datepart(yy,@Datetime)+'N'+datepart(MM,@Datetime)+'N'+@i='11'
set @i=@i+1
end
我要打印出来的样子是:
2008-09-1 2008-09-02 2008-09-03 ...
11 11 11怎解?
declare @Datetime varchar(20)
declare @i int
--设置日期为30天
set @countdate=30
set @Datetime='2008-09-08'
while @i<@countdate
begin
select datepart(yy,@Datetime)+'N'+datepart(MM,@Datetime)+'N'+@i='11'
set @i=@i+1
end
我要打印出来的样子是:
2008-09-1 2008-09-02 2008-09-03 ...
11 11 11怎解?
declare @countdate int
declare @Datetime varchar(20)
declare @i int
set @i=1
--设置日期为30天
set @countdate=30
set @Datetime='2008-09-08'
while @i<@countdate
begin
select cast(datepart(yy,@Datetime) as varchar(20))+'-'+cast(datepart(MM,@Datetime) as varchar(20))+'-'+cast(@i as varchar(5))+char(10)+char(13)+'11'
select @i = @i +1
end
/*
---------------------------------------------------
2008-9-111(1 行受影响)
---------------------------------------------------
2008-9-211(1 行受影响)
---------------------------------------------------
2008-9-311(1 行受影响)
---------------------------------------------------
2008-9-411(1 行受影响)
---------------------------------------------------
2008-9-511(1 行受影响)
---------------------------------------------------
2008-9-611(1 行受影响)
---------------------------------------------------
2008-9-711(1 行受影响)
---------------------------------------------------
2008-9-811(1 行受影响)
---------------------------------------------------
2008-9-911(1 行受影响)
---------------------------------------------------
2008-9-1011(1 行受影响)
---------------------------------------------------
2008-9-1111(1 行受影响)
---------------------------------------------------
2008-9-1211(1 行受影响)
---------------------------------------------------
2008-9-1311(1 行受影响)
---------------------------------------------------
2008-9-1411(1 行受影响)
---------------------------------------------------
2008-9-1511(1 行受影响)
---------------------------------------------------
2008-9-1611(1 行受影响)
---------------------------------------------------
2008-9-1711(1 行受影响)
---------------------------------------------------
2008-9-1811(1 行受影响)
---------------------------------------------------
2008-9-1911(1 行受影响)
---------------------------------------------------
2008-9-2011(1 行受影响)
---------------------------------------------------
2008-9-2111(1 行受影响)
---------------------------------------------------
2008-9-2211(1 行受影响)
---------------------------------------------------
2008-9-2311(1 行受影响)
---------------------------------------------------
2008-9-2411(1 行受影响)
---------------------------------------------------
2008-9-2511(1 行受影响)
---------------------------------------------------
2008-9-2611(1 行受影响)
---------------------------------------------------
2008-9-2711(1 行受影响)
---------------------------------------------------
2008-9-2811(1 行受影响)
---------------------------------------------------
2008-9-2911(1 行受影响)*/
declare @Datetime datetime,@str varchar(max)=''
declare @i int=0
--设置日期为30天
set @countdate=30
set @Datetime='2008-09-08'
while @i<@countdate
begin
set @str=@str+','+convert(varchar(10),dateadd(day,@i,@Datetime),120)
set @i=@i+1
end
exec('select'+stuff(@str,1,1,' '))
declare @Datetime varchar(20)
declare @i int
declare @sql varchar(8000)
--设置日期为30天
set @countdate=30
set @Datetime='2008-09-08'
set @i=1
while @i<@countdate
begin
set @sql=isnull(@sql+',','')+'['+left(@datetime,8)+right('00'+ltrim(@i),2)+']=11'
set @i=@i+1
endexec ('select '+@sql)
我动态打印的列前面有段数据是以查询出来的
也就是说列名不是以日期为标题的数据!本来是打算与是日期汇总! 你用exec打印后可以动态汇总吗?
declare @countdate int
declare @Datetime datetime
declare @i int
declare @s1 as varchar(8000)
declare @s2 as varchar(8000)
--设置日期为30天
set @countdate=30
set @Datetime='2008-09-08'
set @i = 0
set @s1 = 'create table tb('
set @s2 = 'insert into tb values('
while @i <= @countdate
begin
set @s1 = @s1 + '[' + convert(varchar(10),@Datetime + @i ,120) + '] varchar(10),'
set @s2 = @s2 + '11,'
set @i=@i+1
end
set @s1 = left(@s1,len(@s1)-1) + ')'
set @s2 = left(@s2,len(@s2)-1) + ')'exec(@s1)
exec(@s2)select * from tbdrop table tb/*
2008-09-08 2008-09-09 2008-09-10 2008-09-11 2008-09-12 2008-09-13 2008-09-14 2008-09-15 2008-09-16 2008-09-17 2008-09-18 2008-09-19 2008-09-20 2008-09-21 2008-09-22 2008-09-23 2008-09-24 2008-09-25 2008-09-26 2008-09-27 2008-09-28 2008-09-29 2008-09-30 2008-10-01 2008-10-02 2008-10-03 2008-10-04 2008-10-05 2008-10-06 2008-10-07 2008-10-08
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11 11(所影响的行数为 1 行)
*/
union all 可以连接动态的吗?