查找担保圈-step1-担保圈表函数
1 USE [test]; 2 GO 3 /****** Object: UserDefinedFunction [dbo].[f_findrecycle] Script Date: 2019/7/8 14:37:08 ******/ 4 SET ANSI_NULLS ON; 5 GO 6 SET QUOTED_IDENTIFIER ON; 7 GO 8 ALTER FUNCTION [dbo].[f_findrecycle] 9 ( 10 @cname NVARCHAR(100), --起点 11 @nlevel INT --递归的层数 12 ) 13 RETURNS TABLE 14 AS 15 RETURN 16 ( 17 WITH cte_A (kmmc, bzrmc, isrecycle, full_path, nlevel) 18 AS (SELECT [KHMC], 19 [BZRMC], 20 0 AS isrecycle, 21 CAST(KHMC + '<' + BZRMC AS NVARCHAR(MAX)) AS full_path, 22 1 AS nlevel 23 FROM [dbo].[dbgx] 24 WHERE KHMC = @cname 25 UNION ALL 26 SELECT s.KHMC, 27 s.BZRMC, 28 CASE 29 WHEN s.BZRMC = @cname THEN 30 1 --如果下层找到了起点,就停止递归 31 ELSE 32 0 33 END AS isrecycle, 34 CAST(p.full_path + '<' + s.BZRMC AS NVARCHAR(MAX)) AS full_path, 35 p.nlevel + 1 AS nlevel 36 FROM [dbo].[dbgx] AS s 37 INNER JOIN cte_A AS p 38 ON s.KHMC = p.bzrmc 39 WHERE CHARINDEX(s.BZRMC, p.full_path) IN ( 0, 1 ) --防止找到起点(担保圈)后继续进行递归查找 40 AND p.isrecycle = 0 --上层没有找到起点,继续递归 41 AND p.nlevel <= @nlevel --递归的层数 42 ) 43 SELECT a.kmmc, 44 a.bzrmc, 45 a.isrecycle, 46 a.full_path, 47 a.nlevel 48 FROM cte_A AS a 49 WHERE a.isrecycle = 1 50 );
posted on 2019-07-08 14:39 hold_on_up 阅读(198) 评论(0) 编辑 收藏 举报