34,588
社区成员
发帖
与我相关
我的任务
分享
create function subDepartment(@id varchar(36),@flag int)
returns @re table(subid varchar(36))
as
begin
insert @re select deli_id from departmentlink where deli_type=32
and deli_id =@id
if @flag=0
return
while @@rowcount>0
begin
insert @re
select a.deli_id from departmentlink a ,@re b
where a.deli_type=32 and a.deli_depa_id =b.subid
and a.deli_id not in (select subid from @re)
end
return
end
if not object_id('tb') is null drop table tb
IF OBJECT_ID('FUN_MU') IS NOT NULL DROP FUNCTION FUN_MU
Go
CREATE FUNCTION FUN_MU(@ID INT,@FID INT)
RETURNS BIT
AS
BEGIN
DECLARE @TEMPID INT
SELECT @TEMPID=PARENTID FROM TB WHERE ID=@ID
WHILE @TEMPID<>@FID AND @TEMPID IS NOT NULL
BEGIN
IF EXISTS
(SELECT PARENTID FROM TB WHERE ID=@TEMPID)
SELECT @TEMPID=PARENTID FROM TB WHERE ID=@TEMPID
ELSE
SELECT @TEMPID=NULL
END
IF @TEMPID=@FID
--AND NOT EXISTS(SELECT 1 FROM TB WHERE PARENTID=@ID)
RETURN 1
RETURN 0
END
GO
Create table tb([id] int,[parentid] int,[name] varchar(10))
Insert tb
SELECT 1, 0 ,'体育用品' UNION ALL
SELECT 2, 0 ,'户外运动' UNION ALL
SELECT 3, 1 ,'篮球' UNION ALL
SELECT 4, 1 ,'足球' UNION ALL
SELECT 5, 2 ,'帐篷' UNION ALL
SELECT 6, 2 ,'登山鞋' UNION ALL
SELECT 7, 0 ,'男士用品' UNION ALL
SELECT 8, 7 ,'刮胡刀' UNION ALL
SELECT 9, 3 ,'大号篮球'
Go
DECLARE @FATHER INT
SET @FATHER=1
SELECT * FROM TB WHERE DBO.FUN_MU(ID,@FATHER)=1
/*
3 1 篮球
4 1 足球
9 3 大号篮球
*/