我现在用sql =SELECT count(*) as Num, LeisureProjects as Type FROM T_Questionnaire_Master group by LeisureProjects得到一个dataset
num type
--------------------------------
3 aaa;bbb;ccc
1 bbb;ccc
2 ccc;eee
num 是数量 type是多种类型用“;”隔开
我现在想按类型统计数量就是类似如下
num type
--------------------------------
3 aaa
4 bbb
6 ccc
2 eee
表结构:
两种写法
1第一种、
insert into ttt select a.num, substring(a.[type], b.number, charindex(',', a.[type] + ',', b.number) - b.number)
FROM Statistic a, master..spt_values b
WHERE b.type='p' and substring(',' + a.[type],b.number, 1) = ','
statistic是我的表结构: num,type
建一个表 ttt和statistic一样
然后运行上面的语句
ttt表的记录将是以下结果:
下面就简单了。
2 第二种、
set nocount on
select top 100 id=identity(int,1,1),convert(varchar(20), ' ') as a into #o from sysobjects
select ServiceType,sum(Number) SumNumber from(select
substring(TempType,id,charindex( ';',TempType+ ';',id)-id) ServiceType , Number
from (SELECT count(*) as Number, TempType=LeisureProjects FROM T_Questionnaire_Master group by LeisureProjects) c,#o b
where substring( ';'+TempType,id,1)= ';')aa group by ServiceType
drop table #o
一步完成。