怎样将一个表里的多条明细的某个字段提取出来相加放到另一个表的字段里？
本帖最后由 comcyd 于 2013-07-27 17:40:05 编辑 如下图，有一个表A:
A01(序号) A02(内容)
--------------------
1 a
2 b
3 c
....
现要将表A的A02的内容全部取出来用短线连接组成如下格式放到另一个表B的B01字段
B01
-------
a-b-c-... SQL
[解决办法]
insert B(B01) select distinct stuff((select '-'+A02 from A order by A01 for xml path('')),1,1,'')
IF OBJECT_ID('Ta') IS NOT NULL
DROP TABLE Ta
GO
CREATE TABLE Ta
(A01 INT,
A02 VARCHAR(10)
)
INSERT Ta
SELECT 1,'a' UNION
SELECT 2,'b' UNION
SELECT 3,'c'
GO
IF OBJECT_ID('dbo.f_a') IS NOT NULL
DROP FUNCTION dbo.f_a
GO
CREATE FUNCTION f_a()
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @A02 VARCHAR(8000)
SELECT @A02=ISNULL(@A02+'-','')+ A02
From Ta a
RETURN @A02
END
SELECT B01=dbo.f_a()
/*
B01
-------------
a-b-c
(1 行受影响)
*/
IF OBJECT_ID('Ta') IS NOT NULL
DROP TABLE Ta
GO
CREATE TABLE Ta (
A01 VARCHAR(10), A02 VARCHAR(10)
)
INSERT Ta
SELECT 'a001','a' UNION
SELECT 'a002','b' UNION
SELECT 'a003','c'
IF OBJECT_ID('Tb') IS NOT NULL
DROP TABLE Tb
GO
CREATE TABLE Tb
(
B01 VARCHAR(10),
B02 VARCHAR(10)
)
INSERT Tb
SELECT 'a001','11' UNION
SELECT 'a001','22' UNION
SELECT 'a001','33' UNION
SELECT 'a002','44' UNION
SELECT 'a003','55' UNION
SELECT 'a003','66'
GO
IF OBJECT_ID('dbo.f_a') IS NOT NULL
DROP FUNCTION dbo.f_a
GO
CREATE FUNCTION f_a(@B01 Varchar(10))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @A02 VARCHAR(8000)
SELECT @A02=ISNULL(@A02+'-','')+ B02 From Tb Where B01=@B01
RETURN @A02
END
SELECT A01,A02=dbo.f_a(A01) from Ta
/*
A01A02
-------------------
a00111-22-33
a00244
a00355-66
(3 row(s) affected)
*/