对查询的所有行重复输出列

Question

我下面有我的密码

SELECT DISTINCT PCD.CHANGE_ID PARENT_CHANGE_ID, 
NULL as PARENT_TASK_ID,
ICD.CHANGE_ID INFRA_CHANGE_ID,
ICT.TASK_ID INFRA_TASK_ID,
WI.NOTES SERVER_COUNT
FROM 
V_ITSM_REPORT_CHANGE_DATA PCD
JOIN V_ITSM_REPORT_CHANGE_TASK PCT ON PCD.CHANGE_ID=PCT.CHANGE_ID
LEFT JOIN V_ITSM_REPORT_CHANGE_RELATIONS CR ON PCD.CHANGE_ID=CR.REQUEST_ID
LEFT JOIN V_ITSM_REPORT_CHANGE_DATA ICD ON ICD.CHANGE_ID=CR.CHANGE_ID
JOIN V_ITSM_REPORT_CHANGE_TASK ICT ON ICT.CHANGE_ID=ICD.CHANGE_ID
LEFT JOIN V_ITSM_REPORT_CHANGE_TASK_WI WI ON WI.TASK_ID = ICT.TASK_ID AND WI.SUMMARY='DBCPROVISIO'

输出在下面

​

我想在这里对查询的所有行重复最后一列的输出，例如

​

Answer 1

您可以按以下方式使用FIRST_VALUE分析功能：

SELECT DISTINCT PCD.CHANGE_ID PARENT_CHANGE_ID, 
NULL as PARENT_TASK_ID,
ICD.CHANGE_ID INFRA_CHANGE_ID,
ICT.TASK_ID INFRA_TASK_ID,
-- Change are as following
CASE WHEN WI.NOTES = NULL THEN FIRST_VALUE(WI.NOTES) 
OVER (PARTITION BY PCD.CHANGE_ID ORDER BY WI.NOTESDESC NULLS LAST) 
ELSE WI.NOTES END AS SERVER_COUNT
FROM 
V_ITSM_REPORT_CHANGE_DATA PCD
JOIN V_ITSM_REPORT_CHANGE_TASK PCT ON PCD.CHANGE_ID=PCT.CHANGE_ID
LEFT JOIN V_ITSM_REPORT_CHANGE_RELATIONS CR ON PCD.CHANGE_ID=CR.REQUEST_ID
LEFT JOIN V_ITSM_REPORT_CHANGE_DATA ICD ON ICD.CHANGE_ID=CR.CHANGE_ID
JOIN V_ITSM_REPORT_CHANGE_TASK ICT ON ICT.CHANGE_ID=ICD.CHANGE_ID
LEFT JOIN V_ITSM_REPORT_CHANGE_TASK_WI WI ON WI.TASK_ID = ICT.TASK_ID AND WI.SUMMARY='DBCPROVISIO'

Answer 2

如果最后一列中有多个值，则可以根据需要使用max或min。

    SELECT PCD.change_id PARENT_CHANGE_ID,
           NULL          AS PARENT_TASK_ID,
           ICD.change_id INFRA_CHANGE_ID,
           ICT.task_id   INFRA_TASK_ID,
           Min(WI.notes) SERVER_COUNT
    FROM   v_itsm_report_change_data PCD
           join v_itsm_report_change_task PCT
             ON PCD.change_id = PCT.change_id
           left join v_itsm_report_change_relations CR
                  ON PCD.change_id = CR.request_id
           left join v_itsm_report_change_data ICD
                  ON ICD.change_id = CR.change_id
           join v_itsm_report_change_task ICT
             ON ICT.change_id = ICD.change_id
           left join v_itsm_report_change_task_wi WI
                  ON WI.task_id = ICT.task_id
                     AND WI.summary = 'DBCPROVISIO'
    GROUP  BY PCD.change_id,
              NULL,
              ICD.change_id,
              ICT.task_id