这是从以前解决的查询继续（在不同的表中返回迄今为止最近的日期），但是，我现在希望进一步开发它。

我的 SQL 是这样的：

SELECT *
FROM (SELECT O_ASSESSMENTS.ASM_SUBJECT_ID as "P Number", 
             O_ASSESSMENTS.ASM_ID as "Assessment ID", 
             O_ASSESSMENTS.ASM_START_DATE as "Assessment Start",  
             O_ASSESSMENTS.ASM_END_DATE as "Assessment End", 
             O_SERVICE_EVENTS.SEV_ID as "Event ID", 
             O_SERVICE_EVENTS.SEV_ACTUAL_DATE as "Event Start", 
             O_SERVICE_EVENTS.SEV_OUTCOME_DATE as "Event End",
             ROUND(O_ASSESSMENTS.ASM_START_DATE -O_SERVICE_EVENTS.SEV_ACTUAL_DATE,0) as "Likely",
             row_number() over(PARTITION BY  O_ASSESSMENTS.ASM_ID                                    
                               ORDER BY abs(O_ASSESSMENTS.ASM_START_DATE -  O_SERVICE_EVENTS.SEV_ACTUAL_DATE))as "Row Number"            
      FROM O_ASSESSMENTS 
      JOIN O_SERVICE_EVENTS
        ON O_ASSESSMENTS.ASM_SUBJECT_ID = O_SERVICE_EVENTS.SEV_SUBJECT_ID
      Where O_SERVICE_EVENTS.SEV_CODE IN ('ICS_E3','CPINVEST') AND 
            O_ASSESSMENTS.ASM_QSA_ID  IN ('AA1329','AA521') )
WHERE "Row Number" = 1

基本上，我们有两个表 - o_assessments 和 o_service_events，该 SQL 将最近的服务事件返回给评估。现在我想在查询中包含更多上下文信息，以使其对最终用户更有帮助 - 即团队名称和工作人员姓名。

不幸的是，团队和工作人员又在不同的表中（_责任），并通过 asm_id 到 res_rec_i链接到o_assessments。

问题是，我真的不知道如何将这个表调用到上面的 sql 中 - 所以任何建议都将不胜感激！

我还想知道是否可以调整现有查询以仅在“可能”字段的差异为 0 时返回相应的事件（因为更大的值可能与该特定评估无关）。我知道我可以将它添加到 where，但是如果我添加该行

and "Likely" = 0

它只返回带有事件的评估，不会突出任何问题（即没有相应事件的评估）。

我一直在学习 SQL，但目前有很多事情似乎真的超出了我的范围，因此非常感谢任何建议！我不确定是修改我的原始查询还是开始新的查询，所以我希望我没有无意中违反任何规则。

编辑：

好的，按照马克的解决方案，这就是我的工作。

SELECT 
* 
FROM 
(SELECT 
OAS.ASM_SUBJECT_ID as "P Number",
OAS.ASM_ID as "Assessment ID",
OAS.ASM_START_DATE as "Assessment Start", 
OAS.ASM_END_DATE as "Assessment End",
OAS.ASM_AUTH_DATETIME as "Authorisation Date",
nvl(olm_bo.get_ref_desc(OAS.ASM_OUTCOME,'ASM_OUTCOME'),'') as "Outcome",
nvl(olm_bo.get_org_name(ORE.RES_PARTY_OUN_ID),'') as "Team",
nvl(olm_bo.get_per_name(ORE.RES_PARTY_ID),'') as "Worker",
OSE.SEV_ID as "Event ID",
OSE.SEV_ACTUAL_DATE as "Event Start",
OSE.SEV_OUTCOME_DATE as "Event End",
ROUND(OAS.ASM_START_DATE -OSE.SEV_ACTUAL_DATE,0) as "Likely",
row_number() over(PARTITION BY  OAS.ASM_ID                   
ORDER BY 
abs(OAS.ASM_START_DATE -  OSE.SEV_ACTUAL_DATE))as "Row Number"
FROM O_ASSESSMENTS OAS      
INNER JOIN O_RESPONSIBILITIES ORE ON OAS.ASM_ID = ORE.RES_REC_ID
AND ORE.RES_PARTY_OUN_ID = 'TEAM'
LEFT JOIN O_SERVICE_EVENTS OSE ON OAS.ASM_SUBJECT_ID = OSE.SEV_SUBJECT_ID 
AND            
OSE.SEV_CODE IN ('EVENT') 
AND             
ROUND(OAS.ASM_START_DATE - OSE.SEV_ACTUAL_DATE,0) >= -7
AND
ROUND(OAS.ASM_START_DATE - OSE.SEV_ACTUAL_DATE,0) <= 7
Where OAS.ASM_QSA_ID  IN ('ACODE')
AND
nvl(olm_bo.get_ref_desc(OAS.ASM_OUTCOME,'ASM_OUTCOME'),'') <> 'Abandon' ) WHERE "Row Number" = 1