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(Face) select 'B '
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
select A= sum(case brow when 'A ' then 1 else 0 end),B= sum(case brow when 'B ' then 1 else 0 end),C= sum(case brow when 'C ' then 1 else 0 end),D= sum(case brow when 'D ' then 1 else 0 end) from t
A B C D
----------- ----------- ----------- -----------
3 2 1 2
[解决办法]
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 '
declare @sql varchar(2000)
select @sql=isnull(@sql+ ', ', ' ')+
quotename(brow)+ '=sum(case brow when ' ' '+brow+ ' ' ' then 1 else 0 end) '
from t group by brow
order by count(*)desc,brow asc
select @sql= 'select '+@sql+ ' from t '
exec(@sql)
A B D C
----------- ----------- ----------- -----------
3 2 2 1
[解决办法]
declare @sql nvarchar(8000)
set @sql = 'select '
select @sql = @sql + quotename(表情) + '=sum(case when 表情= ' + quotename(表情, ' ' ' ') + ' then 1 else 0 end), '
from Table
group by 表情
select @sql = left(@sql, len(@sql)-1), @sql = @sql+ ' from table '
exec(@sql)
[解决办法]
transform sum(id)
select sum(id) from 表 group by 表情
pivot 表情
看看这种类似的结构吧
