消除递归SQL重复

Question

我的脚本带来了重复的名字，我有问题，我不知道如何修复它。我的脚本将列串联成一行。基本上，我有管理名称，我需要列出在建筑物的同一行和列，他们被分配。管理名称被多次列出，当我连接行时，它列出管理名称如下(John \ John )。联系人和列表有时包含不同的名称。我需要排除名单上重复的名字。我已经附上了一个屏幕截图的当前和期望的结果。使用db2数据库，但不确定版本。谢谢你的帮助。

       WITH
    /*****************************************************
    *** The cte was used to generate test data easily. ***
    *****************************************************/
    sample_data
( rownum, project_id , project_name ,  name_last , name_first, point_of_contact, building_id, building_name, hours_used,
hours_to_use, percentage_used, capability, bucket_a, bucket_b, bucket_c  ) AS
(
VALUES
  (1, 10 , 'ELITE', 'Gamb', 'Susan', 'Susan Gamb | Amber Jones', 2, 'Main' , 80, 0, 1.0, 6000, 12000, 9600, 5100)
, (2, 10 , 'ELITE', 'Gamb', 'Susan', 'Susan Gamb | Amber Jones', 2 , 'Main', 80,  0, 1.0, 7000, 12500, 8000, 4000)
, (3, 10 , 'ELITE', 'Jones', 'Amber', 'Susan Gamb | Amber Jones', 2, 'Main', 80, 0, 1.0, 6000, 12000, 9600, 5100)
, (4, 10 , 'ELITE', 'Jones', 'Amber', 'Susan Gamb | Amber Jones', 2, 'Main', 80, 0, 1.0, 7000, 12500, 8000, 4000)
, (1, 10 , 'ELITE', 'Gamb', 'Susan', 'Susan Gamb | Amber Jones', 6 ,'Warehouse', 40, 40, .5, 6000, 12500, 9600, 5100)
, (2, 10 , 'ELITE', 'Gamb', 'Susan', 'Susan Gamb | Amber Jones', 6, 'Warehouse' , 40, 40, .5, 7000, 12000, 8000, 4000)
, (3, 10 , 'ELITE', 'Jones', 'Amber',  'Susan Gamb | Amber Jones', 6, 'Warehouse' , 40, 40, .5, 6000, 12500, 9600, 5100)
, (4, 10 , 'ELITE', 'Jones', 'Amber',  'Susan Gamb | Amber Jones', 6, 'Warehouse' , 40, 40, .5, 6000, 12000, 8000, 4000 )
, (1, 1040 , 'ROADRUNNER', 'Sugar', 'Paul', 'Paul Sugar | Rob Brown', 2 , 'Main', 60,  20, .75, 5000, 1000, 1200, 4100 )
, (2, 1040 , 'ROADRUNNER', 'Sugar', 'Paul', 'Paul Sugar | Rob Brown', 2, 'Main',  60, 20, .75, 4000, 1500, 1000, 3000)
, (3, 1040 , 'ROADRUNNER', 'Brown', 'Rob', 'Paul Sugar | Rob Brown', 2, 'Main', 60, 20, .75, 4500, 2000, 1200, 4100)
, (4, 1040 , 'ROADRUNNER', 'Brown', 'Rob', 'Paul Sugar | Rob Brown', 2, 'Main', 60, 20, .75, 4000, 1500, 1000, 3000)
, (1, 1040 , 'ROADRUNNER', 'Sugar', 'Paul', 'Paul Sugar | Rob Brown',  6 , 'Warehouse', 60, 20, .75, 4500, 2000, 1200, 4100)
, (2, 1040 , 'ROADRUNNER', 'Sugar', 'Paul', 'Paul Sugar | Rob Brown',  6, 'Warehouse', 60, 20, .75, 4000, 1500, 1000, 3000)
, (2, 1040 , 'ROADRUNNER',  'Brown', 'Rob', 'Paul Sugar | Rob Brown',  6, 'Warehouse', 60, 20, .75, 4500, 2000, 1200, 4100 )
, (3, 1040 , 'ROADRUNNER',  'Brown', 'Rob', 'Paul Sugar | Rob Brown',  6, 'Warehouse', 60, 20, .75, 4000, 1500, 1000, 3000)

)
    ,

           t2(PROJECT_ID, LIST, POINT_OF_CONTACT, PROJECT_NAME, BUILDING_ID, BUILDING_NAME, HOURS_USED, HOURS_TO_USE, PERCENTAGE_USED, CAPABILITY,
           BUCKET_A, BUCKET_B, BUCKET_C, cnt) AS
        ( SELECT    PROJECT_ID,
                    VARCHAR(NAME_FIRST CONCAT ' ' CONCAT NAME_LAST, 6000),
                    POINT_OF_CONTACT,
                    PROJECT_NAME,
                    BUILDING_ID,
                    BUILDING_NAME,
                    HOURS_USED,
                    HOURS_TO_USE,
                    PERCENTAGE_USED,
                    CAPABILITY,
                    BUCKET_A,
                    BUCKET_B,
                    BUCKET_C,
                    1
        FROM SAMPLE_DATA
                WHERE rowNum = 1
                UNION ALL
        SELECT
            t2.PROJECT_ID,
                    t2.list || ' | ' || SAMPLE_DATA.NAME_FIRST CONCAT ' ' CONCAT SAMPLE_DATA.NAME_LAST,
                    SAMPLE_DATA.POINT_OF_CONTACT,
                    SAMPLE_DATA.PROJECT_NAME,
                    SAMPLE_DATA.BUILDING_ID,
                    SAMPLE_DATA.BUILDING_NAME,
                    SAMPLE_DATA.HOURS_USED,
                    SAMPLE_DATA.HOURS_TO_USE,
                    SAMPLE_DATA.PERCENTAGE_USED,
                    SAMPLE_DATA.CAPABILITY,
                    SAMPLE_DATA.BUCKET_A,
                    SAMPLE_DATA.BUCKET_B,
                    SAMPLE_DATA.BUCKET_C,
                    t2.cnt + 1
        FROM t2, SAMPLE_DATA

                WHERE t2.PROJECT_ID = SAMPLE_DATA.PROJECT_ID
                AND          t2.BUILDING_ID = SAMPLE_DATA.BUILDING_ID
                AND   t2.cnt + 1 = SAMPLE_DATA.rowNum
                 )    
        SELECT
                   PROJECT_ID,
                   PROJECT_NAME,
                   POINT_OF_CONTACT,
                   BUILDING_ID,
                   BUILDING_NAME,
                   HOURS_USED,
                   HOURS_TO_USE,
                   PERCENTAGE_USED,
                   CAPABILITY,
                   BUCKET_A,
                   BUCKET_B,
                   BUCKET_C,
                   list
        FROM t2
                WHERE ( PROJECT_ID, BUILDING_ID, cnt ) IN (
        SELECT PROJECT_ID, BUILDING_ID, MAX(rowNum)
        FROM SAMPLE_DATA
                GROUP BY PROJECT_ID, BUILDING_ID )    
                order by PROJECT_NAME                  

预期结果：

​

Answer 1

我能够在MSSQL服务器中测试您的查询并使其正常工作。我尽了最大努力在db2中查找相应的函数。即使这件事行不通，我也希望它能让你走上正确的道路。

在t2的第二个选择中，我更改了：

t2.list || ' | ' || SAMPLE_DATA.NAME_FIRST CONCAT ' ' CONCAT SAMPLE_DATA.NAME_LAST,

至

        WHEN LOCATE(SAMPLE_DATA.NAME_FIRST CONCAT ' ' CONCAT SAMPLE_DATA.NAME_LAST, t2.list) > 0 THEN t2.list
        ELSE t2.list || ' | ' || SAMPLE_DATA.NAME_FIRST || ' ' || SAMPLE_DATA.NAME_LAST
        END,

基本上，我们正在检查这个人是否已经在列表中，如果是，返回当前列表，如果不是，将他们添加到列表中。