如何针对一条记录评估多条记录，以找出缺少匹配值？

Question

我正在创建一些QA/QC查询来清理我的数据，并且我正在尝试查找两个表之间是否缺少匹配值。我总共有三个表:检查(INSP)、风险评估(RA)和工作指令(WO)。检查生成风险评估(INSP.GlobalID = RA.InspectionGlobalID)，并且是一对多。风险评估生成工单，并且是多对一(RA.WorkOrderGlobalID = WO.GlobalID)。检查和工单是1比1 (INSP.GlobalID = WO.InspectionGlobalID)。它们都有一个“优先级”字段，它是一个小整数，范围从0到12，表示工作指令的重要性和完成时间(12是最关键的)。我遇到的问题是，可能有多个RA记录与单个工作指令相关联。我正在尝试寻找在风险评估优先级和工作订单优先级之间缺少匹配值的实例。例如，可能有3个RA的优先级为6、8、10。工作订单的优先级可能为8(这是可接受的)，在这种情况下，我不想选择这些RA中的任何一个，因为组中存在匹配的优先级，但我的查询是从组中选择6和10个优先级RA。如何评估与INSP关联的所有RA，并选择两个表(RA/WO)之间根本没有匹配优先级的记录。

SELECT
RA.Priority,
WO.Priority , 
RA.InspectionGlobalID as RA_INSP_GLBID,
WO.InspectionGlobalID,
RA.WorkOrderGlobalID as RAWOGLBID,
WO.GlobalID,
WO.OBJECTID as WOID, 
INSP.GlobalID,
INSP.OBJECTID as INID,
RA.OBJECTID RA_OBJECTID

FROM 
CFAdmin.RISKASSESSMENT_EVW as RA INNER JOIN
CFAdmin.WORKORDER_EVW AS WO ON WO.GlobalID = RA.WorkOrderGlobalID LEFT OUTER JOIN
CFAdmin.INSPECTION_EVW as INSP ON INSP.GlobalID = RA.InspectionGlobalID LEFT OUTER JOIN
CFAdmin.PLANTINGSPACE_EVW as PS ON INSP.PlantingSpaceGlobalID = PS.GlobalID 

WHERE
RA.Priority <> WO.Priority AND          
INSP.InspectionDate IS NOT NULL AND             
(INSP.CreatedDate > '7/1/2018') AND             
WO.CancelDate IS NULL AND
WO.Status <>2 AND                               
(WO.CreatedDate > '7/1/2018') AND                            
WO.Type NOT IN (17,18, 44,45,3) AND            
WO.WOEntity = 0

Answer 1

将SQL简化为与这个问题相关的部分：(您甚至没有使用来自PLANTINGSPACE_EVW的任何东西)。

SELECT RA.Priority
, WO.Priority
, RA.WorkOrderGlobalID as RAWOGLBID
, WO.GlobalID

FROM CFAdmin.RISKASSESSMENT_EVW as RA
  INNER JOIN CFAdmin.WORKORDER_EVW AS WO ON WO.GlobalID = RA.WorkOrderGlobalID
                                        AND WO.Priority <> RA.Priority

WHERE WO.CancelDate IS NULL
  AND WO.Status <> 2
  AND WO.CreatedDate > cast('7/1/2018' as date)
  AND WO.Type NOT IN (17, 18, 44, 45, 3)
  AND WO.WOEntity = 0

您应该获取优先级不匹配的所有记录。

我无法在没有样本数据的情况下进行测试，但是这样如何？

SELECT RA.Priority
, WO.Priority
, RA.InspectionGlobalID as RA_INSP_GLBID
, WO.InspectionGlobalID
, RA.WorkOrderGlobalID as RAWOGLBID
, WO.GlobalID
, WO.OBJECTID as WOID
, RA.OBJECTID RA_OBJECTID

FROM CFAdmin.RISKASSESSMENT_EVW as RA
  INNER JOIN CFAdmin.WORKORDER_EVW AS WO ON WO.GlobalID = RA.WorkOrderGlobalID

WHERE WO.GlobalId not in (
    SELECT distinct WO2.GlobalID

    FROM CFAdmin.RISKASSESSMENT_EVW as RA2
      INNER JOIN CFAdmin.WORKORDER_EVW AS WO2 ON WO2.GlobalID = RA2.WorkOrderGlobalID
                                             AND WO2.Priority = RA2.Priority
  )
  AND WO.CancelDate IS NULL
  AND WO.Status <> 2
  AND (WO.CreatedDate > '7/1/2018')
  AND WO.Type NOT IN (17, 18, 44, 45, 3)
  AND WO.WOEntity = 0