最近公司编码结构调整,重新定义了新的一套层级关系,用到了递归查询:
使用with,自定义函数:
CREATE FUNCTION [FN_GETXJBM]
(
@SJBM VARCHAR(20) --上级部门
)
RETURNS @t TABLE ( XJBM VARCHAR(50) )
AS
BEGIN
with temp ( KCBMPQSQGL_PQ, KCBMPQSQGL_SQ)
as
(
select KCBMPQSQGL_PQ, KCBMPQSQGL_SQ
from KCBMPQSQGL
where KCBMPQSQGL_PQ = @SJBM
union all
select a.KCBMPQSQGL_PQ, a.KCBMPQSQGL_SQ
from KCBMPQSQGL a
inner join temp on a.[KCBMPQSQGL_PQ] = temp.KCBMPQSQGL_SQ
)
insert into @t select KCBMPQSQGL_SQ from temp
RETURN
END
select * from FN_GETXJBM('2001')
结果
--------------------------------------------------分割线---------------------------------------------------------
逆向递归查询,并只返回最上层:
定义的是标量值函数:
create function [FN_GETSJBM_END](@XJBM VARCHAR(20))
returns VARCHAR(20)
as
begin
declare @SJBM varchar(20)
select @SJBM=KCBMZDGL_SJBM from KCBMZDGL where KCBMZDGL_XJBM=@XJBM
while @@ROWCOUNT<>0
begin
select @SJBM=KCBMZDGL_SJBM from KCBMZDGL where KCBMZDGL_XJBM=@SJBM
end
return @SJBM
end