交叉应用删除空值

Question

我想删除列上带有 NULL 值Crit_Value行。感谢您的帮助。

查询：

SELECT DISTINCT GP.PRIORITY_CD,  CODE_Val
    FROM   TCRITERIA_GROUP_PS GP

   CROSS APPLY ( SELECT PV.CODE + ',' 
                     FROM   TCRITERIA_GROUP_PS_VALUE GPV
    --JOIN TCRITERIA_GROUP_PS GP ON GP.CRITERIA_GROUP_PS_ID = GPV.CRITERIA_GROUP_PS_ID
    JOIN TCRITERIA_PS_VALUE PV ON PV.CRITERIA_PS_VALUE_ID = GPV.CRITERIA_PS_VALUE_ID
    JOIN TCRITERIA_PS CP ON PV.CRITERIA_PS_ID = CP.CRITERIA_PS_ID
    WHERE
    PV.PARTNER_ID = 'JETSTAR'
    AND GP.PARTNER_SYS_ID = 'JETSTAR1'
    AND GP.ISO_CNTRY_CD = 'AU'
    AND GP.CRITERIA_GROUP_PS_ID = GPV.CRITERIA_GROUP_PS_ID
    FOR XML PATH('') )  D ( CODE_Val )

查询的结果集：

PRIORITY_CD    CODE_Val
-------------------------
1              NULL
1              AU,AU,AUD,OW,0_1999,
2              NULL
2              AU,AU,AUD,OW,2000_99999,
3              NULL
3              AU,AU,AUD,RT,0_1999,
4              NULL
4              AU,AU,AUD,RT,2000_99999,
5              NULL
5              AU,ALL_EX,AUD,OW,

Answer 1

尝试更改主表和子查询之间的相关性，或添加 where 子句以过滤掉 NULL

SELECT DISTINCT
  GP.PRIORITY_CD
, D.CODE_Val
FROM TCRITERIA_GROUP_PS GP
CROSS APPLY (
      SELECT
        PV.CODE + ','
      FROM TCRITERIA_GROUP_PS_VALUE GPV
      JOIN TCRITERIA_PS_VALUE PV ON PV.CRITERIA_PS_VALUE_ID = GPV.CRITERIA_PS_VALUE_ID
      JOIN TCRITERIA_PS CP ON PV.CRITERIA_PS_ID = CP.CRITERIA_PS_ID
      WHERE PV.PARTNER_ID = 'JETSTAR'
      AND GP.PARTNER_SYS_ID = 'JETSTAR1'
      AND GP.ISO_CNTRY_CD = 'AU'
      AND GP.PRIORITY_CD = GPV.CRITERIA_GROUP_PS_ID -- changed
      FOR xml PATH ('')
    ) D (CODE_Val)
-- WHERE D.CODE_Val IS NOT NULL