你的位置:首页 > 数据库

[数据库]递归实现集合求解


数据库环境:SQL SERVER 2005

  在群里看到一道题,截图如下:

  可能有些朋友还看不懂题目的意思,我们现在就来分析一下。假设有3种产品A、产品B、产品C,产品A和产品B存在替换关系

如果产品A和产品B中任何一种产品和产品C存在替换关系,则它们就是一组。

  实现思路:把存在替换关系的任意2种产品放到集合1中,然后遍历后续产品,如果满足关系的则加入集合1,否则,新建集合2,

遍历符合集合2的产品并加入。

/*测试数据*/WITH  tab     AS ( SELECT  1 id ,            '121500125' fru ,            '121500132' sub_fru        UNION ALL        SELECT  2 id ,            '121500125' fru ,            '5B19A4567R' sub_fru        UNION ALL        SELECT  3 id ,            '121500132' fru ,            '121500125' sub_fru        UNION ALL        SELECT  4 id ,            '121500132' fru ,            '5B19A4567R' sub_fru        UNION ALL        SELECT  5 id ,            '121500178' fru ,            '121500180' sub_fru        UNION ALL        SELECT  6 id ,            '121500180' fru ,            '121500178' sub_fru        UNION ALL        SELECT  7 id ,            '121500180' fru ,            'SB19A46291' sub_fru       ),/*递归,遍历数据加入关系集合*/    t ( id, fru, sub_fru, fru_r, gp )     AS ( SELECT  id ,            fru ,            sub_fru ,            CONVERT(VARCHAR(100), fru + ',' + sub_fru) AS fru_r ,--关系集合            1 AS gp--组号        FROM   tab        WHERE  id = 1        UNION ALL        SELECT  t1.id ,            t1.fru ,            t1.sub_fru ,            CONVERT(VARCHAR(100), CASE WHEN ( CHARINDEX(t1.fru,                               t2.fru_r) > 0                             OR CHARINDEX(t1.sub_fru,                               t2.fru_r) > 0                            )--如果新产品和关系中的产品有关系,则加入                          THEN CASE WHEN CHARINDEX(t1.fru,                               t2.fru_r) = 0                               THEN t2.fru_r                               + ',' + t1.fru                               WHEN CHARINDEX(t1.sub_fru,                               t2.fru_r) = 0                               THEN t2.fru_r                               + ','                               + t1.sub_fru                               ELSE t2.fru_r                            END                          ELSE t1.fru + ','                            + t1.sub_fru--新建另一组的关系集合                       END) AS fru_r ,            CASE WHEN ( CHARINDEX(t1.fru, t2.fru_r) > 0                  OR CHARINDEX(t1.sub_fru, t2.fru_r) > 0                 ) THEN t2.gp               ELSE t2.gp + 1            END AS gp        FROM   tab t1 ,            t t2        WHERE  t1.id = t2.id + 1       ),/*取出每组集合中的最大子集*/    t1     AS ( SELECT  gp AS id ,            MAX(fru_r) AS fru        FROM   t        GROUP BY gp       )/*按逗号分割关系产品*/  SELECT t1.id ,      col = CAST(SUBSTRING(fru, number,                 CHARINDEX(',', fru + ',', number) - number) AS VARCHAR(100))  FROM  t1      CROSS APPLY master..spt_values  WHERE  type = 'P'      AND number >= 1      AND number <= LEN(fru + 'a')      AND CHARINDEX(',', ',' + fru, number) = number

View Code

  当然,也可以在递归的时候就把每组的产品都罗列出来,就省去后面拆分字符串的步骤。不过,这样实现也比较麻烦。

  结果如下图: