select probleType,sum(1)/(select sum(1) from table) as percent from table group by probleType order by percent descORselect probleType,count(*)/(select Count(*) from table) as percent from table group by probleType order by percent desc
用下面的语句分割类型字段,再GROUP BY--------------------------------------- --N要连继, select top 8000 identity(int,1,1) as N into numtab from (select top 100 id=1 from sysobjects) as a, (select top 100 id=1 from sysobjects) as b, (select top 100 id=1 from sysobjects) as c--------------------------------------------------------------------- declare @a table (id int,string varchar(8000)) insert @a select 1 ,'a,b,c,sd,dfsdfg' union select 2, 'a,n,sdf,we,t' union select 3, 's,df,df'select a.*,b.*,id,substring(','+string+',',N+1,charindex(',',','+string+',',N+1)-(N+1)) from @a a,numtab b where substring(','+string+',',N,8000) like ',_%' order by id,N
CrazyFor(蚂蚁) 大哥没看懂:~~~~~~(
--创建数据测试环境 create table #tb(groupId int,probleType varchar(100)) insert into #tb select 1,'问题A' union all select 2,'问题B' union all select 3,'问题A,问题B,问题C' union all select 4,'问题A,问题C'--为字符分拆创建临时表,top 100是根据probleType的长度而定 select top 100 identity(int,1,1) as id into #temp from (select top 100 id=1 from sysobjects) as a, (select top 100 id=1 from sysobjects) as b, (select top 100 id=1 from sysobjects) as c--分离字符串,并得到每个字符串的出现次数 select probleType,cast(sum(1) as float) as 次数 into #result from ( select substring(','+probleType+',',id+1,charindex(',',','+probleType+',',id+1)-(id+1)) as probleType from #tb a,#temp b where substring(','+probleType+',',b.id,8000) like ',_%' ) aa group by probleType order by probleType--最终结果 select probleType,次数,次数/(select sum(次数) from #result) as 出现频率 from #result order by 次数 desc--删除测试环境 drop table #temp,#tb,#result
应该先要拆分吧
请问怎么做呢
from table
group by probleType
order by percent desc
from table
group by probleType
order by percent desc
from table
group by probleType
order by percent desc
groupId probleType
1 问题A
2 问题B
3 问题A,问题B,问题C
4 问题A,问题C
直接group by那么就会得出一上有4个组,但我要的是三个组阿
A出现3次,B出现2次,C出现2次的结果。
请问怎么写
from table
group by probleType
order by percent descORselect probleType,count(*)/(select Count(*) from table) as percent
from table
group by probleType
order by percent desc
--N要连继,
select top 8000 identity(int,1,1) as N into numtab from
(select top 100 id=1 from sysobjects) as a,
(select top 100 id=1 from sysobjects) as b,
(select top 100 id=1 from sysobjects) as c---------------------------------------------------------------------
declare @a table (id int,string varchar(8000))
insert @a select 1 ,'a,b,c,sd,dfsdfg'
union select 2, 'a,n,sdf,we,t'
union select 3, 's,df,df'select a.*,b.*,id,substring(','+string+',',N+1,charindex(',',','+string+',',N+1)-(N+1))
from @a a,numtab b
where substring(','+string+',',N,8000) like ',_%'
order by id,N
create table #tb(groupId int,probleType varchar(100))
insert into #tb
select 1,'问题A'
union all select 2,'问题B'
union all select 3,'问题A,问题B,问题C'
union all select 4,'问题A,问题C'--为字符分拆创建临时表,top 100是根据probleType的长度而定
select top 100 identity(int,1,1) as id into #temp from
(select top 100 id=1 from sysobjects) as a,
(select top 100 id=1 from sysobjects) as b,
(select top 100 id=1 from sysobjects) as c--分离字符串,并得到每个字符串的出现次数
select probleType,cast(sum(1) as float) as 次数
into #result from
(
select substring(','+probleType+',',id+1,charindex(',',','+probleType+',',id+1)-(id+1)) as probleType
from #tb a,#temp b
where substring(','+probleType+',',b.id,8000) like ',_%'
) aa
group by probleType
order by probleType--最终结果
select probleType,次数,次数/(select sum(次数) from #result) as 出现频率
from #result
order by 次数 desc--删除测试环境
drop table #temp,#tb,#result