求一句SQL结果的分析?
BB表的数据格式:
sid sx smoney
1 a 12
1 a 23
1 b 12
1 c 45
declare @s varchar(800)
set @s= 'Select sid '
select @s=@s+ ', [ '+sx+ ']=sum(case when sx= ' ' '+sx+ ' ' ' then smoney else 0 end ) '
from BB
group by sx
select @s
执行结果是:
Select sid, [a ]=sum(case when sx= 'a ' then smoney else 0 end ),
[b ]=sum(case when sx= 'b ' then smoney else 0 end ),
[c ]=sum(case when sx= 'c ' then smoney else 0 end )
不理解为什么结果是这样子的.不懂得SQL是如何执行的,请各位帮忙??
[解决办法]
原因:sx数据类型是char或者nchar,不是varchar或者nvarchar
declare @s varchar(800)
set @s= 'Select sid '
select @s=@s+ ', [ '+rtrim(sx)+ ']=sum(case when sx= ' ' '+rtrim(sx)+ ' ' ' then smoney else 0 end ) '
from BB
group by rtrim(sx)
select @s
[解决办法]
declare @s varchar(800)
set @s= 'Select sid '
select @s=@s+ ', sum(case sx when ' ' '+sx+ ' ' ' then smoney else 0 end )[ ' + sx+ '] '
from (select distinct sx from bb) as a
set @s = @s+ ' from bb group by sid '
select @s
[解决办法]
按照sx分组后，有三条记录
所以，循环了三次
就成那样了
