求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
常见的统计.
现有如下一个表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
常见的统计.
解决方案 »
- sqlserver 表中字段的约束问题
- 求一个SQL
- 100分求UPDATE某个表中的某个字段中含有的某个字符。
- 请教SQL语句(在线等,谢谢~)
- 非执行事务,报“访问接口 SQLNCLI 无法启动分布式事务“错误,请大家帮忙看一下
- 求解:附加数据库时报错,错误信息如下:
- 十万火急!特复杂的过程!
- 一道数据库设计的面试题,考查的重点和供参考的解决方案如
- 寻找一种好的数据库开发组合!高手请你指导一下!!!(在SQL SERVER的基础上)
- 我开发时用ADO访问ACCESS,如果用户那里没有安装任何OFFICE软件,我做安装时怎样才能让用户正常访问ACCESS
- 帮忙看一下
- sqlserver分页如何实现?
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'
goselect
brow,cnt
into #
from
(select brow,count(*) as cnt from t group by brow) a
order by
cnt desc,browdeclare @sql varchar(8000)
set @sql=''select @sql=@sql+',['+brow+']=max(case brow when '''+brow+''' then cnt end)' from # order by cnt descset @sql='select '+stuff(@sql,1,1,'')+' from #'exec(@sql)
/*
A B D C
----------- ----------- ----------- -----------
3 2 2 1
*/
go
drop table t,#
go
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'
godeclare @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 descset @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
-- 创建测试表
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
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 表情
select @sql=left(@sql, len(@sql)-1), @sql=@sql+' from T'
exec(@sql)
--result
A B C D
----------- ----------- ----------- -----------
3 2 1 2
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)
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
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'
godeclare @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 @istrexec (@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 @istrexec (@istr)
--resultselect A=3,B=2,C=1,D=2
A B C D
----------- ----------- ----------- -----------
3 2 1 2select 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
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
quotename为分隔符
quotename(1)=[1]
quotename(1,'''')='1'
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)
select sum(id) from 表 group by 表情
pivot 表情
看看这种类似的结构吧