# SQL语句统计有关问题(2)

www.MyException.Cn  网友分享于：2013-01-13  浏览：19次

insert #t(Face) select 'C '
insert #t(Face) select 'D '
insert #t(Face) select 'A '
insert #t(Face) select 'A '
insert #t(Face) select 'B '
insert #t(Face) select 'D '
Go
-- 执行语句
declare @sql varchar(4000)
set @sql= ' '
select @sql=@sql+ 'sum(case face when ' ' '+Face+ ' ' ' then 1 else 0 end) as '+Face+ ', '
from (select distinct Face from #t) a
set @sql=left(@sql,len(@sql)-1)
exec( 'select '+@sql+ ' from #t ')
Go

------解决方案--------------------
---行列转换

create table T(ID id, 表情 char(1))
go
insert T select 1, 'A '
union all select 2, 'B '
union all select 3, 'C '
union all select 4, 'D '
union all select 5, 'B '
union all select 6, 'C '
union all select 7, 'D '
go
declare @sql varchar(8000)

set @sql= ' '

select @sql=@sql+ ',sum(case when 表情 = ' ' '+ [表情] + ' ' ' then 1 else 0 end) ' ' '+[表情]+ ' ' ' '
from (select distinct 表情 from T)a
order by [表情]

set @sql= 'select '+stuff(@sql,1,1, ' ')+ ' from T '

exec(@sql)
------解决方案--------------------
--加個排序

create table T(ID int, 表情 char(1))
insert T select 1, 'A '
union all select 2, 'B '
union all select 3, 'C '
union all select 4, 'D '
union all select 5, 'A '
union all select 6, 'A '
union all select 7, 'B '
union all select 8, 'D '

declare @sql varchar(8000)
set @sql= 'select '
select @sql=@sql+quotename(表情)+ '=sum(case when 表情= '+quotename(表情, ' ' ' ')+ ' then 1 else 0 end), '
from T
group by 表情
order by count(*) desc
select @sql=left(@sql, len(@sql)-1), @sql=@sql+ ' from T '
exec(@sql)

--result
A B D C
----------- ----------- ----------- -----------
3 2 2 1
------解决方案--------------------
create table t(ID int,brow varchar(6))
insert into t select 1, 'A '
insert into t select 2, 'B '
insert into t select 3, 'C '
insert into t select 4, 'D '
insert into t select 5, 'A '
insert into t select 6, 'A '
insert into t select 7, 'B '
insert into t select 8, 'D '
go

declare @istr varchar(8000)

select @istr= 'select '

select @istr=@istr+brow+ '= '+convert(varchar(100),count(brow))+ ', ' from t group by brow
select @istr=stuff(@istr,len(@istr),1, ' ')
print @istr

exec (@istr)

select @istr= 'select '
select @istr=@istr+brow+ '= sum(case brow when ' ' '+brow+ ' ' ' then 1 else 0 end) '+ ', ' from t group by brow
select @istr=stuff(@istr,len(@istr),1, ' ')+ ' from t '
print @istr

exec (@istr)

--result

select A=3,B=2,C=1,D=2
A B C D
----------- ----------- ----------- -----------
3 2 1 2