想统计每1个name出现的次数,显示结果为: a 2 b 3 c 2---------------- Select name,Count(*) As Count from table1 Group By name Order By name
declare @sql varchar(8000) set @sql='' select @sql=@sql+','''+name+''',sum(case name when '''+rtrim(name)+''' then 1 else 0 end) as ['+rtrim(name)+']' from tb group by name set @sql=Stuff(@sql,1,1,'') exec('select '+@sql+' from tb ')--结果 a 2 b 3 c 2
--改成你的表名后: declare @sql varchar(8000) set @sql='' select @sql=@sql+','''+name+''',sum(case name when '''+rtrim(name)+''' then 1 else 0 end) as ['+rtrim(name)+']' from table1 group by name set @sql=Stuff(@sql,1,1,'') exec('select '+@sql+' from table1 ')
如果要显示结果是 a 2 b 3 c 2 那SQL语句又该怎么写 -------------- 如果固定只有这三行,可以如下这么写,否则就要用动态SQL语句。Select 'a' As a, SUM(Case name When 'a' Then 1 Else 0 End) As acount, 'b' As b, SUM(Case name When 'b' Then 1 Else 0 End) As bcount, 'c' As c, SUM(Case name When 'c' Then 1 Else 0 End) As ccount from table1
--建立测试环境 Create table TEST(name Varchar(10)) --插入数据 Insert TEST Values('a') Insert TEST Values('a') Insert TEST Values('b') Insert TEST Values('b') Insert TEST Values('b') Insert TEST Values('c') Insert TEST Values('c') GO --测试 Declare @S Varchar(8000) Set @S='Select ' Select @S=@S+''''+name+''' As '+name+',SUM(Case name When '''+name+''' Then 1 Else 0 End) As '+name+'count,' from (Select Distinct name from TEST) A Order By name Select @S=Left(@S,Len(@S)-1)+' from TEST' EXEC(@S) --删除测试环境 Drop Table TEST --结果 /* a acount b bcount c ccount a 2 b 3 c 2 */
a 2
b 3
c 2----------------
Select name,Count(*) As Count from table1 Group By name Order By name
set @sql=''
select @sql=@sql+','''+name+''',sum(case name when '''+rtrim(name)+''' then 1 else 0 end) as ['+rtrim(name)+']'
from tb
group by name
set @sql=Stuff(@sql,1,1,'')
exec('select '+@sql+' from tb ')--结果
a 2 b 3 c 2
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+','''+name+''',sum(case name when '''+rtrim(name)+''' then 1 else 0 end) as ['+rtrim(name)+']'
from table1
group by name
set @sql=Stuff(@sql,1,1,'')
exec('select '+@sql+' from table1 ')
如果要显示结果是
a 2 b 3 c 2
那SQL语句又该怎么写
--------------
如果固定只有这三行,可以如下这么写,否则就要用动态SQL语句。Select
'a' As a,
SUM(Case name When 'a' Then 1 Else 0 End) As acount,
'b' As b,
SUM(Case name When 'b' Then 1 Else 0 End) As bcount,
'c' As c,
SUM(Case name When 'c' Then 1 Else 0 End) As ccount
from
table1
Create table TEST(name Varchar(10))
--插入数据
Insert TEST Values('a')
Insert TEST Values('a')
Insert TEST Values('b')
Insert TEST Values('b')
Insert TEST Values('b')
Insert TEST Values('c')
Insert TEST Values('c')
GO
--测试
Declare @S Varchar(8000)
Set @S='Select '
Select @S=@S+''''+name+''' As '+name+',SUM(Case name When '''+name+''' Then 1 Else 0 End) As '+name+'count,'
from (Select Distinct name from TEST) A Order By name
Select @S=Left(@S,Len(@S)-1)+' from TEST'
EXEC(@S)
--删除测试环境
Drop Table TEST
--结果
/*
a acount b bcount c ccount
a 2 b 3 c 2
*/