与paoluo(一天到晚游泳的鱼)有关的一个问题,希望解释一下
小弟正在学T-SQL,看了一下论坛的一些老帖,有一些疑问想问一下
问题来自:
http://community.csdn.net/Expert/topic/5393/5393540.xml?temp=.6558954
--paoluo(一天到晚游泳的鱼) ( )
Create Table table1
(idInt,
shuyuVarchar(10),
pinminInt,
zhuangtaiVarchar(10))
Insert table1 Select 1, 'A ', 11, '@ '
Union All Select 2, 'B ', 11, '@ '
Union All Select 3, 'A ', 22, '# '
Union All Select 4, 'C ', 12, '$ '
Union All Select 5, 'C ', 44, '* '
Union All Select 6, 'B ', 11, '@ '
GO
Declare @S Varchar(8000)
Select @S = 'Select pinmin, shuyu '
Select @S = @S + ' , SUM(Case zhuangtai When ' ' ' + zhuangtai + ' ' ' Then 1 Else 0 End) As ' + QUOTENAME(zhuangtai)
From table1 Group By zhuangtai --我的主要问题在这里
Select @S = @S + ' , Count(*) As zongshu From table1 Group By pinmin, shuyu Order By pinmin, shuyu '
EXEC(@S)
GO
Drop Table table1
我想知道的是:
(Select @S = @S + ' , SUM(Case zhuangtai When ' ' ' + zhuangtai + ' ' ' Then 1 Else 0 End) As ' + QUOTENAME(zhuangtai)
From table1 Group By zhuangtai)
为什么不与
(Select pinmin, shuyu , SUM(Case zhuangtai When '# ' Then 1 Else 0 End) As [#] , SUM(Case zhuangtai When '$ ' Then 1 Else 0 End) As [$] , SUM(Case zhuangtai When '* ' Then 1 Else 0 End) As [*] , SUM(Case zhuangtai When '@ ' Then 1 Else 0 End) As [@]
From table1 Group By zhuangtai)
等价.
(Select @S = @S + ' , SUM(Case zhuangtai When ' ' ' + zhuangtai + ' ' ' Then 1 Else 0 End) As ' + QUOTENAME(zhuangtai)
From table1 Group By zhuangtai)
他的执行过程是怎么样的.这个过程为什么独立执行的时候就会出错.在查询分析器上都没有消息输出
[解决办法]
Select pinmin, shuyu , SUM(Case zhuangtai When '# ' Then 1 Else 0 End) As [#] , SUM(Case zhuangtai When '$ ' Then 1 Else 0 End) As [$] , SUM(Case zhuangtai When '* ' Then 1 Else 0 End) As [*] , SUM(Case zhuangtai When '@ ' Then 1 Else 0 End) As [@]
From table1 Group By zhuangtai
-----
你寫的這個語句，最後的Group By有點問題，應該是Group By pinmin, shuyu .
這樣拼結出來的SQL語句就和你的那個是一樣的了。
