SQL查询求助：实现仅保留特定参考号的零税额采购单据

SELECT 
 --T_PURCHASEINVOICE.C_ID, 
  T_PURCHASEINVOICE.C_DATE, 
  T_PURCHASEINVOICE.C_NUMBER, 
  T_PURCHASEINVOICE.C_NETAMOUNT  , 
  T_PURCHASEINVOICE.C_GROSSAMOUNT, 
  T_PURCHASEINVOICE.C_TAXAMOUNT , 
  T_PURCHASEINVOICE.C_PERIOD,
  T_PURCHASEINVOICE.C_ALTERNATEREFERENCE ,
  T_TAXRATE.C_CODE
FROM 
  T_PURCHASEINVOICE (NOLOCK) 
  JOIN T_PURCHASEINVOICE_TAXLINE (NOLOCK) ON T_PURCHASEINVOICE.C_ID = T_PURCHASEINVOICE_TAXLINE.C__OWNER_ 
  JOIN T_TAXRATE (NOLOCK) ON T_PURCHASEINVOICE_TAXLINE.C_TAXRATE = T_TAXRATE.C_ID 
WHERE 
  T_TAXRATE.C_CODE = '01' 
  AND (
      -- 保留税额不为0的MRINV采购发票
      (T_PURCHASEINVOICE.C_TAXAMOUNT <> 0 AND T_PURCHASEINVOICE.C_ALTERNATEREFERENCE LIKE '%MRINV%')
      -- 新增保留税额为0的MCINV采购发票
      OR (T_PURCHASEINVOICE.C_TAXAMOUNT = 0 AND T_PURCHASEINVOICE.C_ALTERNATEREFERENCE LIKE '%MCINV%')
  )
UNION ALL 
SELECT 
  --T_PURCHASECREDITNOTE.C_ID, 
  T_PURCHASECREDITNOTE.C_DATE, 
  T_PURCHASECREDITNOTE.C_NUMBER, 
  case when T_PURCHASECREDITNOTE.C_NUMBER  = 'PCN' THEN 1 ELSE -1 END * T_PURCHASECREDITNOTE.C_NETAMOUNT , 
  case when T_PURCHASECREDITNOTE.C_NUMBER  = 'PCN' THEN 1 ELSE -1 END * T_PURCHASECREDITNOTE.C_GROSSAMOUNT ,
  case when T_PURCHASECREDITNOTE.C_NUMBER  = 'PCN' THEN 1 ELSE -1 END * T_PURCHASECREDITNOTE.C_TAXAMOUNT ,
  T_PURCHASECREDITNOTE.C_PERIOD,
  T_PURCHASECREDITNOTE.C_ALTERNATEREFERENCE,
  T_TAXRATE.C_CODE 
FROM 
  T_PURCHASECREDITNOTE (NOLOCK) 
  JOIN T_PURCHASECREDITNOTE_TAXLINE (NOLOCK) ON T_PURCHASECREDITNOTE.C_ID = T_PURCHASECREDITNOTE_TAXLINE.C__OWNER_ 
  JOIN T_TAXRATE (NOLOCK) ON T_PURCHASECREDITNOTE_TAXLINE.C_TAXRATE = T_TAXRATE.C_ID 
WHERE 
  T_TAXRATE.C_CODE = '01'
  AND T_PURCHASECREDITNOTE.C_ALTERNATEREFERENCE LIKE '%MRCR%'

• 采购发票部分：通过OR组合两个条件，既保留原需求中税额不为0的MRINV单据，又新增纳入税额为0的MCINV单据，自动排除其他类型的零税额采购发票
• 采购贷项通知单部分：需要保留的零税额单据本身就是MRCR类型，原查询已限定参考号为MRCR，因此直接保留所有该类型单据即可，无需额外过滤税额

内容的提问来源于stack exchange，提问作者RISL2023