如何获取多个条件的最大日期

Question

不太熟悉Oracle特定的SQL语法，更熟悉MS SQL Server。

我在连接时遇到了问题。

具体地说，连接应该允许我返回表中的最近日期。(在SQL Server中，我只会把select top 1 date order by date-对于oracle，这会让人感到困惑！)

下面是我的整个查询，我试图返回select语句中每个日期列的最大日期，前缀为MAX(。(我知道max前缀不是获取我想要的内容的正确语法，我只是在这里列出它，以便您知道我需要返回max date的日期。)如何获取这些列的最新日期？：

SELECT DISTINCT
req.LCR_REQUEST_ID "Request_ID"
,req.MATTER_ID AS "MatterID_lcr"
,req.CUSTOMER_NAME AS "AccountName_lcr"
,req.STATUS AS "CurrentContractStatus_lcr"
,MAX(HIS.RECORDED_DATE) AS "CurrentStatus_ChangeDate"
--,HIS.STATUS AS "ContractStatus_Historic_lcr"
,MAX(HIS_SUB.RECORDED_DATE) AS "Submitted_Status_Date"
--,HIS_NA.RECORDED_DATE AS "NotAssigned_Status_Date"
,MAX(HIS_AtA.RECORDED_DATE) AS "AssignedToAttorney_Status_Date"
,MAX(HIS_SCIN.RECORDED_DATE) AS "InNegotiation_Status_Date"
,MAX(HIS_EXE.RECORDED_DATE) AS "Executed_Status_Date"
,MAX(HIS_CONV.RECORDED_DATE) AS "ConverteToAmend_Status_Date"
,MAX(HIS_DEAD.RECORDED_DATE) AS "Dead_Status_Date"
,MAX(HIS_COMP.RECORDED_DATE) AS "Completed_Status_Date"
,MAX(HIS_EXP.RECORDED_DATE) AS "Expired_Status_Date"
,CASE
WHEN req.DEALSIZE = 0 THEN ''
WHEN req.DEALSIZE = 1 THEN 'Less Than 100K'
when req.DEALSIZE = 2 THEN '100K-500K'
when req.DEALSIZE = 3 THEN '500K-1M'
when req.DEALSIZE = 4 THEN '1M-10M'
when req.DEALSIZE = 5 THEN '>10M'
END
AS "DealSize_lcr"
,cm_out.PTY_NAME AS "AccountName_cm"
,cm_out.PTY_ID AS "PTYID_CM"
,req.customer_id "PtyID_lcr"
,cm_out.EFFECTIVE_DATE "EffDate_lcr"
,cm_out.INITIALTERM_END_DATE "CPEDate_lcr"
,cm_out.CONTRACT_TERMINATION_DATE "FCTDate_lcr"
,cm_out.TERM_NOTICE_NONCOMP_REASON "ReasonForTermination_lcr"
,cm_out.TERM_TYPE "TermType_lcr"
,cm_out.INITIALTERM_DURATION "InitialTerm_lcr"
,cm_out.INVOICE_TIMEMEAS "InitialTermTymeMeas_lcr"
,cm_out.NUMBER_OF_RENEWALS "NumberOfRenewals_lcr"
,req.AMENDMENT "ParentMatterID_lcr"
,prod.rpg "RPG_lcr"
,prod.SALES_PRODUCT_NAME "Product_lcr"
,prod.CONTRACT_TYPE "ContractType_lcr"
,req.CONTRACT_ROLE "ContractRole_lcr"
,cm_out.METADATA_FLAG "MetaFlag_lcr"
,CASE 
WHEN cm_out.DATA_AUTO_IMPORT_YN = 1 THEN 'YES'
WHEN cm_out.REQUESTOR = 'Imported Record' THEN 'YES'
ELSE 'NO'
END
"DataImport_lcr"
,CASE act.actionstatus
WHEN  'Cancel Date Update' THEN 'Yes'
ELSE NULL
END "ActionToCancelAutoDate_lcr"
,req_emp.FIRST_NAME || ' ' || req_emp.LAST_NAME "Requestor_lcr"
,att_emp.FIRST_NAME || ' ' || att_emp.LAST_NAME  "Attorney_lcr"
,req.REQUESTOR_DEPARTMENT "Requestor_Department_lcr"
,req.AGREEMENT_TITLE "AgreementTitle_lcr"
,req.DATE_RECORDED
FROM LCR_REQUEST REQ
LEFT JOIN  LCR_ACTION ACT ON req.MATTER_ID = act.MATTER_ID
LEFT JOIN CM_CONTRACT_OUTBOUND cm_out ON cm_out.MATTER_ID = NVL(req.MATTER_ID,req.AMENDMENT)
LEFT JOIN LCR_REQUEST_PRODUCT prod_REQ ON prod_req.LCR_REQUEST_ID = req.LCR_REQUEST_ID
LEFT JOIN LCR_PRODUCT PROD ON prod.PRODUCT_ID = prod_req.PRODUCT_ID
LEFT JOIN LCR_EMPLOYEE att_emp ON req.ASSIGNED_TO = att_emp.ORACLE_PERSON_ID
LEFT JOIN LCR_EMPLOYEE req_emp ON req.REQUESTED_BY_ID = req_emp .ORACLE_PERSON_ID
LEFT JOIN LCR_STATUS_HISTORY HIS ON HIS.LCR_REQUEST_ID = req.LCR_REQUEST_ID AND ((HIS.STATUS = REQ.STATUS) OR (HIS.STATUS = 'Assigned to Attorney' AND REQ.STATUS = 'Assigned To Attorney'))
--LEFT JOIN LCR_STATUS_HISTORY HIS_NA ON req.LCR_REQUEST_ID = HIS_NA.LCR_REQUEST_ID AND HIS_NA.STATUS = 'Not Assigned'
LEFT JOIN LCR_STATUS_HISTORY HIS_AtA ON req.LCR_REQUEST_ID = HIS_AtA.LCR_REQUEST_ID AND HIS_AtA.STATUS = 'Assigned to Attorney'
LEFT JOIN LCR_STATUS_HISTORY HIS_SUB ON req.LCR_REQUEST_ID = HIS_SUB.LCR_REQUEST_ID AND HIS_SUB.STATUS = 'Submitted'
LEFT JOIN LCR_STATUS_HISTORY HIS_SCIN ON req.LCR_REQUEST_ID = HIS_SCIN.LCR_REQUEST_ID AND HIS_SCIN.STATUS = 'Submission Complete-In Negotiation'
LEFT JOIN LCR_STATUS_HISTORY HIS_EXE ON req.LCR_REQUEST_ID = HIS_EXE.LCR_REQUEST_ID AND HIS_EXE.STATUS = 'Executed'
LEFT JOIN LCR_STATUS_HISTORY HIS_CONV ON req.LCR_REQUEST_ID = HIS_CONV.LCR_REQUEST_ID AND HIS_CONV.STATUS = 'Converted to amendment.'
LEFT JOIN LCR_STATUS_HISTORY HIS_DEAD ON req.LCR_REQUEST_ID = HIS_DEAD.LCR_REQUEST_ID AND HIS_DEAD.STATUS = 'Dead'
LEFT JOIN LCR_STATUS_HISTORY HIS_COMP ON req.LCR_REQUEST_ID = HIS_COMP.LCR_REQUEST_ID AND HIS_COMP.STATUS = 'Completed'
LEFT JOIN LCR_STATUS_HISTORY HIS_EXP ON req.LCR_REQUEST_ID = HIS_EXP.LCR_REQUEST_ID AND HIS_EXP.STATUS = 'Expired/Terminated'
WHERE REQ.CATEGORY_NAME = 'Sales'
AND
REQ.DATE_RECORDED BETWEEN to_date ('07/01/2012', 'mm/dd/yyyy') AND to_date  ('06/30/2013','mm/dd/yyyy')
GROUP BY
req.LCR_REQUEST_ID
,req.MATTER_ID
,req.CUSTOMER_NAME
,req.STATUS
,HIS.RECORDED_DATE
--,HIS.STATUS
,HIS_SUB.RECORDED_DATE
--,HIS_NA.RECORDED_DATE
,HIS_AtA.RECORDED_DATE
,HIS_SCIN.RECORDED_DATE
,HIS_EXE.RECORDED_DATE
,HIS_CONV.RECORDED_DATE
,HIS_DEAD.RECORDED_DATE
,HIS_COMP.RECORDED_DATE
,HIS_EXP.RECORDED_DATE
,CASE
WHEN req.DEALSIZE = 0 THEN ''
WHEN req.DEALSIZE = 1 THEN 'Less Than 100K'
when req.DEALSIZE = 2 THEN '100K-500K'
when req.DEALSIZE = 3 THEN '500K-1M'
when req.DEALSIZE = 4 THEN '1M-10M'
when req.DEALSIZE = 5 THEN '>10M'
END
,cm_out.PTY_NAME
,cm_out.PTY_ID
,req.customer_id
,cm_out.EFFECTIVE_DATE
,cm_out.INITIALTERM_END_DATE
,cm_out.CONTRACT_TERMINATION_DATE
,cm_out.TERM_NOTICE_NONCOMP_REASON
,cm_out.TERM_TYPE
,cm_out.INITIALTERM_DURATION
,cm_out.INVOICE_TIMEMEAS
,cm_out.NUMBER_OF_RENEWALS
,req.AMENDMENT
,prod.rpg
,prod.SALES_PRODUCT_NAME
,prod.CONTRACT_TYPE
,req.CONTRACT_ROLE
,cm_out.METADATA_FLAG
,CASE 
WHEN cm_out.DATA_AUTO_IMPORT_YN = 1 THEN 'YES'
WHEN cm_out.REQUESTOR = 'Imported Record' THEN 'YES'
ELSE 'NO'
END
,CASE act.actionstatus
WHEN  'Cancel Date Update' THEN 'Yes'
ELSE NULL
END
,req_emp.FIRST_NAME || ' ' || req_emp.LAST_NAME
,att_emp.FIRST_NAME || ' ' || att_emp.LAST_NAME
,req.REQUESTOR_DEPARTMENT
,req.AGREEMENT_TITLE
,req.DATE_RECORDED;

Answer 1

我想知道如何使用单个TOP 1获得具有最大日期的多个列？

使用MAX(CASE)可以很容易地用单个连接替换到LCR_STATUS_HISTORY的大多数左连接：

LEFT JOIN
 (
   SELECT LCR_REQUEST_ID,
      MAX(CASE WHEN STATUS = 'Assigned to Attorney' THEN RECORDED_DATE END) AS "AssignedToAttorney_Status_Date"
     ,MAX(CASE WHEN STATUS = 'Submission Complete-In Negotiation' THEN RECORDED_DATE END) AS "InNegotiation_Status_Date"
     ,...
   FROM LCR_STATUS_HISTORY
   GROUP BY LCR_REQUEST_ID
 ) his 
ON HIS.LCR_REQUEST_ID = req.LCR_REQUEST_ID 

那么你可能不再需要这个组了。

在这个连接条件中，OR部分可以被删除：

((HIS.STATUS = REQ.STATUS) OR (HIS.STATUS = 'Assigned to Attorney' AND REQ.STATUS = 'Assigned To Attorney'))
ON HIS.LCR_REQUEST_ID = req.LCR_REQUEST_ID