declare @i int set @i=1 declare @s varchar(8000) set @s='' while @i<10 beginset @s=@s+' insert into testTotal(CNO,Genok,VYes,Yes,NNo,VNO) select a.[A0'+cast(@i as nvarchar(1))+'], sum(a.一般) as 一般,sum(a.非常同意) as 非常同意,sum(a.同意) as 同意, sum(a.不同意) as 不同意,sum(非常不同意) as 非常不同意 from (select ''A0'+cast(@i as nvarchar(1))+' as a0@i,sum(iif(A0'+cast(@i as nvarchar(1))+'=''一般'',1,0)) as 一般, sum(iif(A0'+cast(@i as nvarchar(1))+'=''非常同意'',1,0)) as 非常同意, sum(iif(A0'+cast(@i as nvarchar(1))+'=''同意'',1,0)) as 同意, sum(iif(A0'+cast(@i as nvarchar(1))+'=''不同意'',1,0)) as 不同意, sum(iif(A0'+cast(@i as nvarchar(1))+'=''非常不同意'',1,0)) as 非常不同意 from testtable group by A0'+cast(@i as nvarchar(1))+' ) a group by a.A0@i'set @i=@i+1 end print @s
我调试了很久,快差不多了,但就是卡在这里group by 后面的写不对:declare @i int set @i=4 while @i<10 begin insert into testtotal(CNO,Genok,VYes,Yes,NNo,VNO) select a.[A0+cast(@i as nvarchar(1))], sum(a.一般) as 一般,sum(a.非常同意) as 非常同意, sum(a.同意) as 同意, sum(a.不同意) as 不同意,sum(非常不同意) as 非常不同意 from (select 'A0'+cast(@i as nvarchar(1)) as [A0+cast(@i as nvarchar(1))], sum(iif(A0+cast(@i as nvarchar(1))='一般',1,0)) as 一般, sum(iif(A0+cast(@i as nvarchar(1))='非常同意',1,0)) as 非常同意, sum(iif(A0+cast(@i as nvarchar(1))='同意',1,0)) as 同意, sum(iif(A0+cast(@i as nvarchar(1))='不同意',1,0)) as 不同意, sum(iif(A0+cast(@i as nvarchar(1))='非常不同意',1,0)) as 非常不同意 from testtable group by [A0+cast(@i as nvarchar(1))] ) a group by a.[A0+cast(@i as nvarchar(1))] set @i=@i+1 end 麻烦再看看,谢谢!
别名是不能作为分组条件的 'A0'+cast(@i as nvarchar(1) 本来这是没问题的 但是你到后面就一律用别名了 这是行不通的
set @i=1
declare @s varchar(8000)
set @s=''
while @i<10
beginset @s=@s+'
insert into testTotal(CNO,Genok,VYes,Yes,NNo,VNO)
select a.[A0'+cast(@i as nvarchar(1))+'],
sum(a.一般) as 一般,sum(a.非常同意) as 非常同意,sum(a.同意) as 同意,
sum(a.不同意) as 不同意,sum(非常不同意) as 非常不同意
from (select ''A0'+cast(@i as nvarchar(1))+' as a0@i,sum(iif(A0'+cast(@i as nvarchar(1))+'=''一般'',1,0)) as 一般,
sum(iif(A0'+cast(@i as nvarchar(1))+'=''非常同意'',1,0)) as 非常同意,
sum(iif(A0'+cast(@i as nvarchar(1))+'=''同意'',1,0)) as 同意,
sum(iif(A0'+cast(@i as nvarchar(1))+'=''不同意'',1,0)) as 不同意,
sum(iif(A0'+cast(@i as nvarchar(1))+'=''非常不同意'',1,0)) as 非常不同意
from testtable group by A0'+cast(@i as nvarchar(1))+' ) a group by a.A0@i'set @i=@i+1
end
print @s
set @i=4
while @i<10
begin
insert into testtotal(CNO,Genok,VYes,Yes,NNo,VNO)
select a.[A0+cast(@i as nvarchar(1))], sum(a.一般) as 一般,sum(a.非常同意) as 非常同意,
sum(a.同意) as 同意,
sum(a.不同意) as 不同意,sum(非常不同意) as 非常不同意
from
(select 'A0'+cast(@i as nvarchar(1)) as [A0+cast(@i as nvarchar(1))],
sum(iif(A0+cast(@i as nvarchar(1))='一般',1,0)) as 一般,
sum(iif(A0+cast(@i as nvarchar(1))='非常同意',1,0)) as 非常同意,
sum(iif(A0+cast(@i as nvarchar(1))='同意',1,0)) as 同意,
sum(iif(A0+cast(@i as nvarchar(1))='不同意',1,0)) as 不同意,
sum(iif(A0+cast(@i as nvarchar(1))='非常不同意',1,0)) as 非常不同意
from testtable group by [A0+cast(@i as nvarchar(1))] ) a
group by a.[A0+cast(@i as nvarchar(1))]
set @i=@i+1
end
麻烦再看看,谢谢!