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