查找担保圈-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 阅读(147) 评论(0) 编辑 收藏 举报