/*
问题列表:
问题1:根据行业Id取出所有下属行业
*/--表结构以及初始化数据 start
CREATE TABLE HY
(
Id int primary key ,--行业ID
UpId int,--绑定行业ID
[Name] varchar(50)--行业名
)
GOINSERT INTO HY
SELECT 1,0,'工业' UNION ALL
SELECT 2,0,'农业' UNION ALL
SELECT 3,1,'汽车业' UNION ALL
SELECT 4,3,'卡车' UNION ALL
SELECT 5,3,'小轿车' UNION ALL
SELECT 6,2,'种植业' UNION ALL
SELECT 7,6,'种棉花'
问题列表:
问题1:根据行业Id取出所有下属行业
*/--表结构以及初始化数据 start
CREATE TABLE HY
(
Id int primary key ,--行业ID
UpId int,--绑定行业ID
[Name] varchar(50)--行业名
)
GOINSERT INTO HY
SELECT 1,0,'工业' UNION ALL
SELECT 2,0,'农业' UNION ALL
SELECT 3,1,'汽车业' UNION ALL
SELECT 4,3,'卡车' UNION ALL
SELECT 5,3,'小轿车' UNION ALL
SELECT 6,2,'种植业' UNION ALL
SELECT 7,6,'种棉花'
id 是个集合就不行了吧
对的,这里要用游标了,顺序取出ID来执行
execute test(select Id from HY where UpId=0)楼主可以查查游标的语法
CREATE FUNCTION f_Cid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3),Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level
FROM hy a,@t_Level b
WHERE a.upid=b.ID
AND b.Level=@Level-1
END
RETURN
END
GOSELECT a.*
FROM hy a,f_Cid('2') b
WHERE a.ID=b.ID
(
Id int primary key ,--行业ID
UpId int,--绑定行业ID
[Name] varchar(50)--行业名
);
GO
INSERT INTO HY
SELECT 1,0,'工业' UNION ALL
SELECT 2,0,'农业' UNION ALL
SELECT 3,1,'汽车业' UNION ALL
SELECT 4,3,'卡车' UNION ALL
SELECT 5,3,'小轿车' UNION ALL
SELECT 6,2,'种植业' UNION ALL
SELECT 7,6,'种棉花'
;
GO
-------------------------------------------------------------------
-------------------------------------------------------------------
CREATE FUNCTION [dbo].[FUNC_HY](@pid int/*要查找的id*/,@cid int/*当前的id*/)
RETURNS int
AS
BEGIN
declare @ret int ;
declare @t int ;
set @ret = 0; /*如果不需要包含自身, 注释掉下面的语句, lb_loop之前*/
if @pid = @cid
begin
set @ret = 1;
goto lb_exit ;
end lb_loop:
select @t = isnull(Upid, -1) from dbo.HY where [Id] = @cid ;
if @t >= 0
begin
if @t = @pid
begin
set @ret = 1;
goto lb_exit ;
end
else
begin
set @cid = @t ;
set @t = (-1);
goto lb_loop ;
end
end
lb_exit:
return @ret;
END
GO
-------------------------------------------------------------------
-------------------------------------------------------------------
select * from dbo.HY where [dbo].[FUNC_HY](0, [Id])=1;
select * from dbo.HY where [dbo].[FUNC_HY](1, [Id])=1;
select * from dbo.HY where [dbo].[FUNC_HY](2, [Id])=1;
select * from dbo.HY where [dbo].[FUNC_HY](3, [Id])=1;