有个表
id belongToId
== ================
1 2
2 3
3 4
4 5怎样select的结果是1,2,3,4,5也就是用户给个id是1...把属于他的所有的子id都给select出来
id belongToId
== ================
1 2
2 3
3 4
4 5怎样select的结果是1,2,3,4,5也就是用户给个id是1...把属于他的所有的子id都给select出来
解决方案 »
- SQL查询问题,大家看看,用 LIKE '%%'与SELECT * FROM TABLE有什么区别
- 达们帮忙啊把MS2000数据库文件xx.mdf附加到ms2005数据库中
- 请问在dts中建立包后,在哪里可以使用dtsrun命令执行包的操作啊?
- 跨服务器连接数据库报错
- 问一个 数据库的问题 困扰很久了
- win7下怎么安Sql2005
- 错误提示:“多步操作产生错误,请检查每一步的状态值”,是怎么回事啊?
- 通过链接服务器向远程服务器写记录时出现乱码,困扰好多天了,请帮忙,谢谢!
- 大家进来看...在线急等存储过程.的问题
- 交叉调用存储过程的问题
- bcp 把数据传输,不同表顺序
- 这个sql语句什么问题?
create table BOM(ID VARCHAR(10),PID VARCHAR(10))
insert into BOM select 'a',NULL
insert into BOM select 'b','a'
insert into BOM select 'c','a'
insert into BOM select 'd','b'
insert into BOM select 'e','b'
insert into BOM select 'f','c'
insert into BOM select 'g','c'
go--创建用户定义函数
create function f_getChild(@ID VARCHAR(10))
returns varchar(8000)
as
begin
declare @i int,@ret varchar(8000)
declare @t table(ID VARCHAR(10),PID VARCHAR(10),Level 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
select @ret = isnull(@ret,'')+ID from @t
return @ret
end
go--执行查询
select ID,isnull(dbo.f_getChild(ID),'') from BOM group by ID
go--输出结果
/*
a bcdefg
b de
c fg
d
e
f
g
*/--删除测试数据
drop function f_getChild
drop table BOM
id int,
belongToId int
)
GOinsert into TESTTAB(id,belongToId)
select 1,2
UNION ALL select 2,3
UNION ALL select 3,4
UNION ALL select 4,5
GODECLARE @RESULTSTR VARCHAR(4000)
DECLARE @CUR_LV INT
DECLARE @LAST_LV INTSELECT @CUR_LV=1 --此处为入口
SELECT @LAST_LV=0
SELECT @RESULTSTR=RTRIM(CAST (@CUR_LV AS CHAR(3)))
WHILE @LAST_LV<>@CUR_LV
BEGIN
SELECT @LAST_LV=@CUR_LV
SELECT @RESULTSTR=@RESULTSTR+','+RTRIM(CAST (belongToId AS CHAR(3))),@CUR_LV=belongToId
FROM TESTTAB WHERE id=@CUR_LV
ENDSELECT @RESULTSTR---------------------
1,2,3,4,5(1 行受影响)
CREATE FUNCTION GET_ALLLV(@LV INT)
returns varchar(8000)
as
BEGIN
DECLARE @RESULTSTR VARCHAR(4000)
DECLARE @CUR_LV INT
DECLARE @LAST_LV INT SELECT @CUR_LV=@LV --此处为入口
SELECT @LAST_LV=-1
SELECT @RESULTSTR=RTRIM(CAST (@CUR_LV AS CHAR(3)))
WHILE @LAST_LV<>@CUR_LV
BEGIN
SELECT @LAST_LV=@CUR_LV
SELECT @RESULTSTR=@RESULTSTR+','+RTRIM(CAST (belongToId AS CHAR(3))),@CUR_LV=belongToId
FROM TESTTAB WHERE id=@CUR_LV
END
RETURN @RESULTSTR
END
GOSELECT id,dbo.GET_ALLLV(id) FROM TESTTABid
--------------------------------
1 1,2,3,4,5
2 2,3,4,5
3 3,4,5
4 4,5(4 行受影响)