排除从 case 语句派生的行，这些行具有基于另一列中的值的特定值

Question

如何根据另一个字段的特定值排除字段中具有特定值的行？要更深入地了解：

我有一个名为"rule_name"的列，它派生自案例语句。

我有另一列名为"DeliveryTermsIdentifier"，它是从另一个表中提取的(请参阅下面的查询结果图像(。

仅当交付条款标识符为"PP"、"DA"等时，我才尝试删除带有"需要交付事件">rule_name行。我被甩掉了，因为我的rule_name列已经基于一个条件。

查询结果的图像

以下是用于派生这 2 列的 SQL：

,case when s.ShipmentNo is not null then cast(dqr.rulename as varchar(30))
else cast(dqc.dataqualityruleid as varchar(30)) end as rule_name
,case when (dqd.SuccessFlag = 0) then 'Missing'
when (dqd.SuccessFlag = 1) then 'Reported'
when (s.ShipmentNo is null) then 'Missing' end reporting_result
,s.DeliveryTermsIdentifier
from CustomerMasterList cml
join organization o on o.organizationid = cml.senderid
inner join DataQualityConfig dqc on dqc.DomainId = cml.DomainID
left outer join shipment s on s.shipmentno = cml.shipmentno
left outer join dataqualityresult dqd on dqd.shipmentid = s.shipmentid 
left outer join dataqualityrule dqr on dqr.dataqualityruleid 
=dqd.dataqualityruleid 

Answer 1

一种方法是将您拥有的内容放在子查询中，并在外部查询中使用 where 子句进行过滤。 例如

SELECT rule_name, reporting_result, DeliveryTermsIdentifier
FROM
(SELECT 'X'
,case when s.ShipmentNo is not null then cast(dqr.rulename as varchar(30))
else cast(dqc.dataqualityruleid as varchar(30)) end as rule_name
,case when (dqd.SuccessFlag = 0) then 'Missing'
when (dqd.SuccessFlag = 1) then 'Reported'
when (s.ShipmentNo is null) then 'Missing' end reporting_result
,s.DeliveryTermsIdentifier
from CustomerMasterList cml
join organization o on o.organizationid = cml.senderid
inner join DataQualityConfig dqc on dqc.DomainId = cml.DomainID
left outer join shipment s on s.shipmentno = cml.shipmentno
left outer join dataqualityresult dqd on dqd.shipmentid = s.shipmentid 
left outer join dataqualityrule dqr on dqr.dataqualityruleid 
=dqd.dataqualityruleid ) AS sub
WHERE DeliveryTermsIdentifier NOT IN ('PP', 'DA') AND rule_name <> 'Delivery event required'

Answer 2

您还可以将 case 语句封装到外部应用程序中，并使用别名，如下所示：

SELECT * ,
rules.rule_name ,
RESULT.reporting_result ,
s.DeliveryTermsIdentifier
FROM   CustomerMasterList cml
JOIN organization o ON o.organizationid = cml.senderid
INNER JOIN DataQualityConfig dqc ON dqc.DomainId = cml.DomainID
LEFT OUTER JOIN shipment s ON s.shipmentno = cml.shipmentno
LEFT OUTER JOIN dataqualityresult dqd ON dqd.shipmentid = s.shipmentid
LEFT OUTER JOIN dataqualityrule dqr ON dqr.dataqualityruleid = dqd.dataqualityruleid
OUTER APPLY (   SELECT CASE WHEN ( dqd.SuccessFlag = 0 ) THEN 'Missing'
WHEN ( dqd.SuccessFlag = 1 ) THEN 'Reported'
WHEN ( s.ShipmentNo IS NULL ) THEN 'Missing'
END reporting_result ) RESULT
OUTER APPLY (   SELECT CASE WHEN s.ShipmentNo IS NOT NULL THEN
CAST(dqr.rulename AS VARCHAR(30))
ELSE
CAST(dqc.dataqualityruleid AS VARCHAR(30))
END AS rule_name ) rules
WHERE  s.DeliveryTermsIdentifier NOT IN ( 'PP', 'DA' )
AND rules.rule_name <> 'Delivery event required';