查找担保圈-step4-提取s1中担保圈路径中的成员 

 1 USE [test]
 2 GO
 3 /****** Object:  StoredProcedure [dbo].[p02_get_group_member]    Script Date: 2019/7/8 14:58:03 ******/
 4 SET ANSI_NULLS ON
 5 GO
 6 SET QUOTED_IDENTIFIER ON
 7 GO
 8 ALTER PROC [dbo].[p02_get_group_member]
 9 AS
10 --s2，提取s1中担保圈路径中的成员
11 BEGIN
12     IF OBJECT_ID(N'dbo.t02_get_group_member') IS NOT NULL
13         DROP TABLE dbo.t02_get_group_member;
14     SELECT b.id,
15            b.cchar AS cust_name,
16            a.groupno,
17            a.nlevel
18     INTO dbo.t02_get_group_member --将循环链条的拆分结果输出到表中
19     FROM dbo.t01_get_group_path AS a
20         CROSS APPLY dbo.tf_split_char(a.full_path, '<') AS b;
21     --对上面的表建立索引
22     ALTER TABLE [dbo].t02_get_group_member
23     ALTER COLUMN [groupno] BIGINT NOT NULL;
24 
25     ALTER TABLE [dbo].t02_get_group_member ADD PRIMARY KEY (groupno, id);
26 
27     ALTER TABLE [dbo].t02_get_group_member
28     ALTER COLUMN [cust_name] NVARCHAR(100) NOT NULL;
29 
30     ALTER TABLE [dbo].t02_get_group_member ALTER COLUMN nlevel INT NOT NULL;
31 
32     --建立名称索引
33     CREATE NONCLUSTERED INDEX [NonClusteredIndex-20190523-113242]
34     ON [dbo].t02_get_group_member ([cust_name] ASC)
35     WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
36           ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
37          );
38 END;

 

posted on 2019-07-08 14:59  hold_on_up  阅读(151)  评论(0)  编辑  收藏  举报