SQL语句统计问题
求SQL(MSSQL)


现有如下一个表

ID               表情
=============
1                   A
2                   B
3                   C
4                   D
5                   A
6                   A
7                   B
8                   D
..............

现在我想分不同表情统计个数并按照多少排序.

结果应该是

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

select
brow,cnt
into #
from
(select brow,count(*) as cnt from t group by brow) a
order by
cnt desc,brow

declare @sql varchar(8000)
set @sql= ' '

select @sql=@sql+ ',[ '+brow+ ']=max(case brow when ' ' '+brow+ ' ' ' then cnt end) ' from # order by cnt desc

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

exec(@sql)
/*
A B D C
----------- ----------- ----------- -----------
3 2 2 1
*/
go


drop table t,#
go
------解决方案--------------------
化简一下，不用临时表过渡：

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 @sql varchar(8000)
set @sql= ' '

select @sql=@sql+ ',[ '+brow+ ']=max(case brow when ' ' '+brow+ ' ' ' then cnt end) ' from (select brow,count(*) as cnt from t group by brow) a order by cnt desc

set @sql= 'select '+stuff(@sql,1,1, ' ')+ ' from (select brow,count(*) as cnt from t group by brow) a '

exec(@sql)
/*
A B D C
----------- ----------- ----------- -----------
3 2 2 1
*/
go


drop table t
go
------解决方案--------------------
-- try!
-- 创建测试表
create table #t([ID] int identity(1,1),Face varchar(10))
Go
-- 插入测试数据
insert #t(Face) select 'A '
insert #t(Fac