# SQL语句统计有关问题

www.MyException.Cn  网友分享于：2013-01-13  浏览：19次
SQL语句统计问题

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(Face) select 'B '