查找担保圈-step5-比较各组之间的成员,对组的包含性进行查询,具体见程序的注释-版本1
1 USE [test] 2 GO 3 /****** Object: StoredProcedure [dbo].[p03_get_groupno_e1] Script Date: 2019/7/8 14:59:39 ******/ 4 SET ANSI_NULLS ON 5 GO 6 SET QUOTED_IDENTIFIER ON 7 GO 8 ALTER PROC [dbo].[p03_get_groupno_e1] 9 AS 10 --s3,比较各组之间的成员,对组的包含性进行查询,具体见程序的注释 11 BEGIN 12 SET STATISTICS TIME off; 13 SET STATISTICS IO off; 14 SET NOCOUNT ON ; 15 DECLARE @get_max_groupno INT = 0; 16 SELECT @get_max_groupno = MAX(groupno) 17 FROM dbo.t01_get_group_path; 18 TRUNCATE TABLE dbo.t03_get_groupno; --预先清空dbo.t03_get_groupno表 19 DECLARE @a INT = 1; 20 WHILE @a <= @get_max_groupno --最大的分组编号 21 BEGIN 22 DECLARE @b INT = @a + 1; 23 WHILE @b <= @get_max_groupno --最大的分组编号 24 BEGIN 25 BEGIN 26 IF EXISTS 27 ( 28 SELECT cust_name --第一种情况a包含b,且b不包含a,即a-b不为空,b-a为空 29 FROM [dbo].t02_get_group_member 30 WHERE groupno = @a 31 EXCEPT 32 SELECT cust_name 33 FROM [dbo].t02_get_group_member 34 WHERE groupno = @b 35 ) 36 AND NOT EXISTS 37 ( 38 SELECT cust_name 39 FROM [dbo].t02_get_group_member 40 WHERE groupno = @b 41 EXCEPT 42 SELECT cust_name 43 FROM [dbo].t02_get_group_member 44 WHERE groupno = @a 45 ) 46 BEGIN 47 INSERT INTO dbo.t03_get_groupno 48 ( 49 ctype, 50 iinclude_groupno, 51 ibeincluded_groupno 52 ) 53 VALUES 54 (1, @a, @b); 55 END; 56 ELSE IF EXISTS 57 ( 58 SELECT cust_name --第二种情况b包含a,且a不包含b,即b-a不为空,a-b为空 59 FROM [dbo].t02_get_group_member 60 WHERE groupno = @b 61 EXCEPT 62 SELECT cust_name 63 FROM [dbo].t02_get_group_member 64 WHERE groupno = @a 65 ) 66 AND NOT EXISTS 67 ( 68 SELECT cust_name 69 FROM [dbo].t02_get_group_member 70 WHERE groupno = @a 71 EXCEPT 72 SELECT cust_name 73 FROM [dbo].t02_get_group_member 74 WHERE groupno = @b 75 ) 76 BEGIN 77 INSERT INTO dbo.t03_get_groupno 78 ( 79 ctype, 80 iinclude_groupno, 81 ibeincluded_groupno 82 ) 83 VALUES 84 (2, @b, @a); 85 END; 86 ELSE IF EXISTS 87 ( 88 SELECT cust_name --第三种情况a不包含b,且b不包含a,即a-b不为空,b-a也不为空 89 FROM [dbo].t02_get_group_member 90 WHERE groupno = @a 91 EXCEPT 92 SELECT cust_name 93 FROM [dbo].t02_get_group_member 94 WHERE groupno = @b 95 ) 96 AND EXISTS 97 ( 98 SELECT cust_name 99 FROM [dbo].t02_get_group_member 100 WHERE groupno = @b 101 EXCEPT 102 SELECT cust_name 103 FROM [dbo].t02_get_group_member 104 WHERE groupno = @a 105 ) 106 BEGIN 107 INSERT INTO dbo.t03_get_groupno 108 ( 109 ctype, 110 iinclude_groupno, 111 ibeincluded_groupno 112 ) 113 VALUES 114 (3, @a, 0), 115 (3, @b, 0); 116 END; 117 ELSE IF NOT EXISTS 118 ( 119 SELECT cust_name --第四种情况a包含b,且b包含a,即a和b是元素完全相同的组,a集合保留,b集合不保留或者a集合不保留,b集合保留 120 FROM [dbo].t02_get_group_member 121 WHERE groupno = @a 122 EXCEPT 123 SELECT cust_name 124 FROM [dbo].t02_get_group_member 125 WHERE groupno = @b 126 ) 127 AND NOT EXISTS 128 ( 129 SELECT cust_name 130 FROM [dbo].t02_get_group_member 131 WHERE groupno = @b 132 EXCEPT 133 SELECT cust_name 134 FROM [dbo].t02_get_group_member 135 WHERE groupno = @a 136 ) 137 BEGIN 138 INSERT INTO dbo.t03_get_groupno 139 ( 140 ctype, 141 iinclude_groupno, 142 ibeincluded_groupno 143 ) 144 VALUES 145 (4, @a, @b); 146 END; 147 SET @b = @b + 1; 148 END; 149 END; 150 SET @a = @a + 1; 151 END; 152 END;
posted on 2019-07-08 15:00 hold_on_up 阅读(136) 评论(0) 编辑 收藏 举报