SQL Server 分组统计
我想请教下大家关于SQL查询统计的问题：
直接上图：
我分别想统计出：选项select_key 每个的个数 注意：select_key= 1,2 表示 选择了1和2
tmbhselect_keyOptionCount
A117
A11,2284
A1214
A131
结果我想要是：
tmbhselect_keyOptionCount
A117+284
A1214+284
A131
各位大侠，帮我看看应该怎么查询统计呢？在线等回复
[解决办法]
create table tb(tmbh varchar(10),select_key varchar(30),OptionCount int)
insert into tb
select 'A1','1',7 union all
select 'A1','1,2',284 union all
select 'A1','2',14 union all
select 'A1','3',1
go
select a.tmbh,a.select_key,a.OptionCount+isnull(b.OptionCount,0) as OptionCount
from tb a
left join tb b
on a.tmbh = b.tmbh
and charindex(',',b.select_key) > 0
and CHARINDEX(','+a.select_key+',' , +','+b.select_key+',')>0
where charindex(',',a.select_key) = 0
/*
tmbhselect_keyOptionCount
A11291
A12298
A131
*/
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-12-30 14:21:51
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([tmbh] varchar(2),[select_key] varchar(3),[OptionCount] int)
insert [huang]
select 'A1','1',7 union all
select 'A1','1,2',284 union all
select 'A1','2',14 union all
select 'A1','3',1
--------------开始查询--------------------------
SELECT tmbh,SUM([OptionCount])[OptionCount],[select_key]
FROM (
select
[tmbh],
a.[OptionCount],
SUBSTRING([select_key],number,CHARINDEX(',',[select_key]+',',number)-number) as [select_key]
from
[huang] a,master..spt_values
where
number >=1 and number<=len([select_key])
and type='p'
and substring(','+[select_key],number,1)=',')a
GROUP BY tmbh,[select_key]
----------------结果----------------------------
/*
tmbh OptionCount select_key
---- ----------- ----------
A1 291 1
A1 298 2
A1 1 3
*/
