DB2中查询到重复字段处理

DB2中查询到重复字段处理

在项目中需要用到下拉框去后台查询数据，显示出来中文名称，传到后台的是名称代号。因为中文名称对应的代号有多个，所以这里需要对sql语句进行处理，在未处理之前，数据显示中文名称会有多个重复。

SELECT DISTINCT
        DEFECT_CODE ,
          CASE
                WHEN DEFECT_CODE='62'
                THEN 'XX'
                WHEN DEFECT_CODE='63'
                THEN 'XXXX'
                WHEN DEFECT_CODE='67'
                THEN 'XXXX'
                WHEN DEFECT_CODE='68'
                THEN 'XXXXXX'
                WHEN DEFECT_CODE='69'
                THEN 'XXXX'
                WHEN DEFECT_CODE='5'
                THEN 'XXXXXX'
                WHEN DEFECT_CODE='4'
                THEN 'XXXX'
                WHEN DEFECT_CODE='35'
                THEN 'XXXX'
                WHEN DEFECT_CODE='39'
                THEN 'XXXX'
                WHEN DEFECT_CODE='42'
                THEN 'XXXX'
                ELSE DEFECT_CNAME
            END AS "DEFECT_CNAME",
            DEFECT_CODE
        FROM
            BGTAMAQA.T_ADS_FACT_HR_DEFECT_STATISTICS
                where DEFECT_CNAME not in ('null')
            ORDER BY 
                DEFECT_CNAME ASC        

image.png

这样传到前台的话，下拉框中也会出现多个重复的中文名称，所以需要对取到的数据进行处理

SELECT
    DEFECT_CNAME AS "defect_cname",
    LISTAGG(DEFECT_CODE,',') AS "defect_code"
FROM
    (
        SELECT distinct
            CASE
                WHEN DEFECT_CODE='62'
                THEN 'XX'
                WHEN DEFECT_CODE='63'
                THEN 'XXXX'
                WHEN DEFECT_CODE='67'
                THEN 'XXXX'
                WHEN DEFECT_CODE='68'
                THEN 'XXXXXX'
                WHEN DEFECT_CODE='69'
                THEN 'XXXX'
                WHEN DEFECT_CODE='5'
                THEN 'XXXXXX'
                WHEN DEFECT_CODE='4'
                THEN 'XXXX'
                WHEN DEFECT_CODE='35'
                THEN 'XXXX'
                WHEN DEFECT_CODE='39'
                THEN 'XXXX'
                WHEN DEFECT_CODE='42'
                THEN 'XXXX'
                ELSE DEFECT_CNAME
            END AS "DEFECT_CNAME",
            DEFECT_CODE
        FROM
            BGTAMAQA.T_ADS_FACT_HR_DEFECT_STATISTICS
        WHERE
            DEFECT_CNAME NOT IN ('null')
        ORDER BY
            DEFECT_CNAME ASC )
GROUP BY
    DEFECT_CNAME

image.png

这样，前端得到的中文名称就不会重复了，之后使用查询的时候，在查询条件中使用字段代号in数据，就可以进行查询