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数据，就可以进行查询
