--生成测试数据
create table BOM(ID INT,PID INT,MSG VARCHAR(1000))
insert into BOM select 1,0,NULL
insert into BOM select 2,1,NULL
insert into BOM select 3,1,NULL
insert into BOM select 4,2,NULL
insert into BOM select 5,3,NULL
insert into BOM select 6,5,NULL
insert into BOM select 7,6,NULL
go--创建用户定义函数用于取每个父节点下子节点的采购配置信息
create function f_getChild(@ID VARCHAR(10))
returns @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT)
as
begin
declare @i int
set @i = 1
insert into @t select ID,PID,@i from BOM where PID = @ID
while @@rowcount<>0
begin
set @i = @i + 1
insert into @t
select
a.ID,a.PID,@i
from
BOM a,@t b
where
a.PID=b.ID and b.Level = @i-1
end
return
end
go--执行查询
select ID from dbo.f_getChild(3)
go--输出结果
/*
ID
----
5
6
7
*/--删除测试数据
drop function f_getChild
drop table BOM
如何得到ID以字符相加形式的结果。如:返回字符串 5,6,7............
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-02 13:43:53
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] varchar)
insert [tb]
select 5 union all
select 6 union all
select 7
--------------开始查询--------------------------
DECLARE @STR VARCHAR(8000)SELECT @STR=ISNULL(@STR+',','')+ID FROM (SELECT DISTINCT ID FROM tb)AS TSELECT @STR
----------------结果----------------------------
/* ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5,6,7(1 行受影响)
*/
DROP TABLE TEST
create table test(id varchar(4))
insert into test
select 5 union all
select 6 union all
select 7
go
select * from testDECLARE @STR VARCHAR(10)SELECT @STR=ISNULL(@STR+',','')+ID FROM (SELECT DISTINCT ID FROM test)AS TSELECT @STR
----------
5,6,7(所影响的行数为 1 行)
create table tb(col1 varchar(10),col2 varchar(10))
insert tb
select
'A', 'A1'
union all select 'A', 'A2'
union all select 'A', 'A3'
union all select 'A', 'A4'
union all select 'B', 'B1'
union all select 'B', 'B2'
goif object_id('f_tb') is not null drop function f_tb
go
create function f_tb(@col1 nvarchar(100))
returns nvarchar(100)
as
begin
declare @sql nvarchar(4000)
set @sql=N''
select @sql=@sql+N','+col2 from tb where col1=@col1
set @sql=stuff(@sql,1,1,N'')
return(@sql)
end
goselect col1,dbo.f_tb(col1) as col2 from tb
group by col1
/*
col1 col2
A A1,A2,A3,A4
B B1,B2
*/字符合并
insert into BOM select 1,0,NULL
insert into BOM select 2,1,NULL
insert into BOM select 3,1,NULL
insert into BOM select 4,2,NULL
insert into BOM select 5,3,NULL
insert into BOM select 6,5,NULL
insert into BOM select 7,6,NULL
gowith k as(
select cast(id as varchar(8000)) as ido ,ID,leve1=1
from BOM where PID=3
union all
select K.ido+','+cast(B.ID as varchar(8000)),B.ID ,leve1+1
from BOM B JOIN K
ON K.ID=b.PID
)
select top 1 ido from k order by leve1 desc ido
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5,6,7
insert tb
select 001, 'AA' union all
select 001, 'BB' union all
select 001, 'CC' union all
select 002, 'DD' union all
select 002, 'EE' union all
select 003, 'FF'
go
CREATE FUNCTION dbo.f_tb(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @str varchar(8000)
SET @str = ''
SELECT @str = @str + ' ' + CardName FROM tb WHERE CardNo=@id
RETURN STUFF(@str, 1, 1, '')
END
GO SELECT CardNo, CardName = dbo.f_tb(CardNo) FROM tb GROUP BY CardNo
drop table tb
drop function dbo.f_tb
go、
参考吧