原表
ID Type(varchar) QTY(int)
1 a,b,c 2
2 b,c 5
3 a,c 1
查询效果
ID Type QTY
1 a 2+1=3 只显示3就好了
2 b 2+5=7
3 c 2+5+1=8
其中a,b,c项数不固定,根据Type来确定 比如 原表中的Type如果有a,b,c 则效果中有 a;b;c
如果原表中有a,b,c,d 则效果中有a;b;c;d
ID Type(varchar) QTY(int)
1 a,b,c 2
2 b,c 5
3 a,c 1
查询效果
ID Type QTY
1 a 2+1=3 只显示3就好了
2 b 2+5=7
3 c 2+5+1=8
其中a,b,c项数不固定,根据Type来确定 比如 原表中的Type如果有a,b,c 则效果中有 a;b;c
如果原表中有a,b,c,d 则效果中有a;b;c;d
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-11-22 10:01:36
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[Type] varchar(5),[QTY] int)
insert [tb]
select 1,'a,b,c',2 union all
select 2,'b,c',5 union all
select 3,'a,c',1
--------------开始查询--------------------------
;with f as
(
Select
a.qty,type=substring(a.type,b.number,charindex(',',a.type+',',b.number)-b.number)
from
Tb a join master..spt_values b
ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.type)
where
substring(','+a.type,b.number,1)=','
)select TYPE,SUM(qty) as qty from f group by type
----------------结果----------------------------
/* TYPE qty
----- -----------
a 3
b 7
c 8(3 行受影响)
*/
insert into tb
select 1,'a,b,c',2
union
select 2,'b,c',5
union
select 3,'a,c',1select id=ROW_NUMBER() over(order by getdate()),
type=substring(a.type,b.number,charindex(',',a.type+',',b.number)-b.number),
qty=SUM(qty)
from tb a,master..spt_values b
where b.[type] = 'p' and b.number between 1 and len(a.type)
and substring(','+a.type,b.number,1) = ','
group by substring(a.type,b.number,charindex(',',a.type+',',b.number)-b.number) /*
id type qty
-------------------- -------------------- -----------
1 a 3
2 b 7
3 c 8(3 行受影响)
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-11-22 10:01:36
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[Type] varchar(5),[QTY] int)
insert [tb]
select 1,'a,b,c',2 union all
select 2,'b,c',5 union all
select 3,'a,c',1
--------------开始查询--------------------------
;with f as
(
Select
a.qty,type=substring(a.type,b.number,charindex(',',a.type+',',b.number)-b.number)
from
Tb a join master..spt_values b
ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.type)
where
substring(','+a.type,b.number,1)=','
)select id=row_number()over(order by getdate()),TYPE,SUM(qty) as qty from f group by type
----------------结果----------------------------
/*id TYPE qty
-------------------- ----- -----------
1 a 3
2 b 7
3 c 8(3 行受影响)*/
go
create table [tb]([ID] int,[Type] varchar(5),[QTY] int)
insert [tb]
select 1,'a,b,c',2 union all
select 2,'b,c',5 union all
select 3,'a,c',1
go
select
b.[Type],SUM(a.[QTY]) AS [QTY]
from
(select [ID],[QTY],[Type]=convert(xml,'<root><v>'+replace([Type],',','</v><v>')+'</v></root>') from [tb])a
outer apply
(select [Type]=C.v.value('.','nvarchar(100)') from a.[Type].nodes('/root/v')C(v))b
GROUP BY b.[Type]/*
Type QTY
a 3
b 7
c 8
*/
把Type存在',',拆分為多條記錄,再合計
http://topic.csdn.net/u/20080612/22/c850499f-bce3-4877-82d5-af2357857872.html
if object_id('tb','U') is not null
drop table tb
go
create table tb
(
id int,
type varchar(10),
qty int
)
go
insert into tb
select 1,'a,b,c',2 union all
select 2,'b,c',5 union all
select 3,'a,c',1
go
select row=row_number() over(order by getdate()) ,type=substring(a.type,number,charindex(',',a.type+',',number)-number),qty=sum(qty) from tb a cross join master..spt_values b where b.type='p' and number between 1 and len(a.type) and substring(','+a.type,number,1)=',' group by substring(a.type,number,charindex(',',a.type+',',number)-number)
/*
row type qty
-------------------- ---------- -----------
1 a 3
2 b 7
3 c 8(3 行受影响)*/