日期:2014-05-17 浏览次数:20581 次
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
/*
tmbh select_key OptionCount
A1 1 291
A1 2 298
A1 3 1
*/
----------------------------------------------------------------
-- 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 &nb