while @i<=31
begin
set @comm='select idno,sum(case when jyrqi=''2009-09-10'''+' then jyl else 0 end) as n'+''''@i''''
print @comm
-- exec(@comm)
set @i=@i+1
end这段代码一直提示 n'+''''@i''''有语法错误,不知道是哪里的问题
begin
set @comm='select idno,sum(case when jyrqi=''2009-09-10'''+' then jyl else 0 end) as n'+''''@i''''
print @comm
-- exec(@comm)
set @i=@i+1
end这段代码一直提示 n'+''''@i''''有语法错误,不知道是哪里的问题
declare @comm nvarchar(1000)
set @i=1
while @i<=31
begin
set @comm='select idno,sum(case when jyrqi=''2009-09-10'' then jyl else 0 end) ['+ltrim(@i)+']'
print @comm
-- exec(@comm)
set @i=@i+1
end
declare @comm nvarchar(1000)
set @i=30
while @i<=31
begin
set @comm='select idno,sum(case when jyrqi=''2009-09-10'' then jyl else 0 end) ['+ltrim(@i)+']'
print @comm
-- exec(@comm)
set @i=@i+1
endselect idno,sum(case when jyrqi='2009-09-10' then jyl else 0 end) [30]
select idno,sum(case when jyrqi='2009-09-10' then jyl else 0 end) [31]
begin
set @comm='select idno,sum(case when jyrqi=''2009-09-10'''+' then jyl else 0 end) as n'''+ltrim(@i)+''''
print @comm
-- exec(@comm)
set @i=@i+1
end
begin
set @comm='select idno,sum(case when jyrqi=''2009-09-10'' then jyl else 0 end) as n'''+ltrim(@i)+''''
print @comm
-- exec(@comm)
set @i=@i+1
end
begin
set @comm='select idno,sum(case when jyrqi=''2009-09-10'''+' then jyl else 0 end) as n'+LTRIM(@i)
print @comm
-- exec(@comm)
set @i=@i+1
end
set @i=1
while @i<=31
begin
set @comm='select idno,sum(case when jyrqi=''2009-09-10'' then jyl else 0 end) as n'+rtrim(@i)
print @comm
-- exec(@comm)
set @i=@i+1
end
/*
select idno,sum(case when jyrqi='2009-09-10' then jyl else 0 end) as n1
select idno,sum(case when jyrqi='2009-09-10' then jyl else 0 end) as n2
select idno,sum(case when jyrqi='2009-09-10' then jyl else 0 end) as n3
select idno,sum(case when jyrqi='2009-09-10' then jyl else 0 end) as n4
select idno,sum(case when jyrqi='2009-09-10' then jyl else 0 end) as n5
select idno,sum(case when jyrqi='2009-09-10' then jyl else 0 end) as n6
select idno,sum(case when jyrqi='2009-09-10' then jyl else 0 end) as n7
select idno,sum(case when jyrqi='2009-09-10' then jyl else 0 end) as n8
......*/
改
n'+convert(varchar(2),@i)
(
idno varchar(15),
dept varchar(6),
lbmc varchar(10),
jyrqi datetime,
jyl decimal(9,2)
)
insert into table1(idno,dept,lbmc,jyrqi,jyl) select idno,dept,lbmc,jyrqi,jyl from oil_record where (dept='箱管部')
and( datediff(mm,jyrqi,'2009-09-30')=0 and datediff(yyyy,jyrqi,'2009-09-30')=0) declare @i int
set @i=1
while @i<=31
begin
select idno,
sum(case when jyrqi='2009-09-01' then jyl else 0 end) as nltrim(@i)
--sum(case when jyrqi='2009-09-02' then jyl else 0 end) as n2,
--sum(case when jyrqi='2009-09-03' then jyl else 0 end) as n3,
--sum(case when jyrqi='2009-09-04' then jyl else 0 end) as n4,
--sum(case when jyrqi='2009-09-05' then jyl else 0 end) as n5,
--sum(case when jyrqi='2009-09-06' then jyl else 0 end) as n6,
--sum(case when jyrqi='2009-09-07' then jyl else 0 end) as n7,
--sum(case when jyrqi='2009-09-08' then jyl else 0 end) as n8,
--sum(case when jyrqi='2009-09-09' then jyl else 0 end) as n9,
--sum(jyl)
from table1
group by idno
set @i=@i+1
end
drop table table1一直提示“as nltrim(@i)”附近语法错误,不知道错在哪里了。
as 'n'+ltrim(cast(@i as varchar(20)))