CREATEFUNCTION f_Pid(@IDchar(3))
RETURNS@t_LevelTABLE(ID char(3),Levelint)
ASBEGINDECLARE@LevelintSET@Level=1INSERT@t_LevelSELECT@ID,@LevelWHILE@@ROWCOUNT>0BEGINSET@Level=@Level+1INSERT@t_LevelSELECT a.PID,@LevelFROM tb a,@t_Level b
WHERE a.ID=b.ID
AND b.Level=@Level-1ENDRETURNENDGO--上面的用户定义函数可以处理一个节点有多个父节点的情况,对于标准的树形数据而言,由于每个节点仅有一个父节点,所以也可以通过下面的用户定义函数实现查找标准树形数据的父节点。CREATEFUNCTION f_Pid(@IDchar(3))
RETURNS@t_LevelTABLE(ID char(3))
ASBEGININSERT@t_LevelSELECT@IDSELECT@ID=PID FROM tb
WHERE ID=@IDAND PID ISNOTNULLWHILE@@ROWCOUNT>0BEGININSERT@t_LevelSELECT@IDSELECT@ID=PID FROM tb
WHERE ID=@IDAND PID ISNOTNULLENDRETURNEND
create table #c(id int,parentid int,sname varchar(10)) insert into #c select 1 , -1 , '根节点' insert into #c select 2 , 1 , '节点1' insert into #c select 3 , 1 , '节点2' insert into #c select 4 , 2 , '节点3' insert into #c select 5 , 2 , '节点4' insert into #c select 6 , 3 , '节点5' insert into #c select 7 , 3 , '节点6' insert into #c select 7 , 6 , '节点7' go
WITH cet AS( SELECT * FROM #c WHERE parentid = 3 -- 要查询的父 id UNION ALL SELECT a.* FROM #c a inner join cet b on a.parentid = b.id ) SELECT * FROM cet where id not in (select parentid from cet)
在2005中执行此功能函数成功,但是在2000中执行失败,错误提示对 INSERT @t_Level SELECT a.ID,@Level FROM tb a,@t_Level b WHERE a.PID=b.ID AND b.Level=@Level-1 这句,理由是 服务器: 消息 446,级别 16,状态 9,过程 f_Cid,行 11 无法解决 equal to 操作的排序规则冲突。
你的说法也就相当于上面写的这段实现的功能 begin INSERT @t_Level select id from tb where parentid=@ID while exists(select * from tb where parentid=@ID and id>@Level) begin set @Level=(select top 1 id from tb where parentid=@ID and id>@Level) --set @ID=@Level INSERT @t_Level select Level from dbo.f_Cid(@Level) end end
已知部门组织结构: -------------------------------------------------------- [PDeptID] [DeptID] [Name] [Description] -------------------------------------------------------- NULL 1 A 1 2 B 1 3 C 1 4 D 3 5 E 3 6 F ... --------------------------------------------------------- 查某部门节点下所有节点函数如下:
CREATE FUNCTION [dbo].[F_UM_DPTS] (@DeptID INT) RETURNS @RET_UM_DPTS TABLE ( [PDeptID] [int] NULL , [DeptID] [int] NOT NULL , [Name] [nvarchar] (50) NULL , [Description] [ntext] NULL ) AS
BEGIN DECLARE @ROWSADDED INT DECLARE @TMP TABLE( [PDeptID] [int] NULL , [DeptID] [int] NOT NULL , [Name] [nvarchar] (50) NULL , [Description] [ntext] NULL , PROCESSED TINYINT DEFAULT 0
)
INSERT @TMP SELECT PDeptID,DeptID,[Name],[Description],0 FROM UM_DPTS WHERE DeptID=@DeptID SELECT @ROWSADDED=@@ROWCOUNT
WHILE @ROWSADDED>0 BEGIN UPDATE @TMP SET PROCESSED=1 WHERE PROCESSED=0
INSERT @TMP SELECT A.PDeptID,A.DeptID,A.[Name],A.[Description],0 FROM UM_DPTS A INNER JOIN @TMP B ON A.PDeptID=B.DeptID WHERE B.PROCESSED=1 SELECT @ROWSADDED=@@ROWCOUNT
UPDATE @TMP SET PROCESSED=2 WHERE PROCESSED=1 END
INSERT @RET_UM_DPTS SELECT PDeptID,DeptID,[Name],[Description] FROM @TMP RETURN END