----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-03-27 16:29:12
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([部位] varchar(1),[物料] varchar(3),[用量] int)
insert [tb]
select 'A','XXX',100 union all
select 'B','XXX',200 union all
select 'C','XXX',300
--------------开始查询--------------------------
select 物料,SUM(用量) AS 用量, [部位]=stuff((select '+'+[部位] from tb where 物料=t.物料 for xml path('')), 1, 1, '')
from tb t
group by 物料
----------------结果----------------------------
/* 物料 用量 部位
---- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
XXX 600 A+B+C(1 行受影响)*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-03-27 16:29:12
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([部位] varchar(1),[物料] varchar(3),[用量] int)
insert [tb]
select 'A','XXX',100 union all
select 'B','XXX',200 union all
select 'C','XXX',300
--------------开始查询--------------------------
select 物料,SUM(用量) AS 用量, [部位]=stuff((select '+'+[部位] from tb where 物料=t.物料 for xml path('')), 1, 1, '')
from tb t
group by 物料
----------------结果----------------------------
/* 物料 用量 部位
---- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
XXX 600 A+B+C(1 行受影响)*/
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-03-27 16:29:34
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[HUANG]
if object_id('[HUANG]') is not null drop table [HUANG]
go
create table [HUANG]([部位] nvarchar(2),[物料] nvarchar(6),[用量] int)
insert [HUANG]
select 'A','XXX',100 union all
select 'B','XXX',200 union all
select 'C','XXX',300
--------------生成数据--------------------------select stuff((select '+'+[部位] from [HUANG] b
where b.[物料]=a.[物料]
for xml path('')),1,1,'') '部位',a.[物料],SUM(a.[用量])[用量]
from [HUANG] a
group by a.[物料]
----------------结果----------------------------
/*
部位 物料 用量
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------ -----------
A+B+C XXX 600*/
--sql2005
select 物料,
(select 部位+'+' from tb b where a.物料 = b.物料 for xml path('')) as 部位,
sum(用量) 用量
from tb a
group by 物料
--sql2005
select 物料,
stuff((select 部位+'+' from tb b where a.物料 = b.物料 for xml path('')),1,1,'') as 部位,
sum(用量) 用量
from tb a
group by 物料
擦!怎么一下冒出来俩
WITH a1 (zd1,zd2,zd3) AS
(
SELECT 'A','XXX',100 UNION ALL
SELECT 'B','XXX',200 UNION ALL
SELECT 'C','XXX',300
)
SELECT STUFF((SELECT '+'+zd1 FROM a1 WHERE zd2=a.zd2 FOR XML PATH('')),1,1,'') zd1,zd2,SUM(zd3) d3
FROM a1 a
GROUP BY zd2
create table test(部位 nvarchar(10),物料 nvarchar(10),用量 int)
insert test
select 'A','XXX',100 union all
select 'B','XXX',200 union all
select 'C','XXX',300
SELECT data=STUFF((SELECT ','+部位 FROM test t
WHERE 物料=t1.物料 FOR XML PATH('')), 1, 1, ''),
物料,
用量=SUM(用量)
FROM test t1
GROUP BY 物料
/*
A,B,C XXX 600
*/
谢谢各位! 测试OK!不过,我不明白这段:
data=STUFF((SELECT ','+部位 FROM test t
WHERE 物料=t1.物料 FOR XML PATH('')), 1, 1, '')