Oracle SQL Subquery with MAX Function in Subquery

Question

我正在使用Oracle SQL Developer开发Oracle DB。 我有两个疑问。 我需要更改查询 #1，以便它从查询 #2 中检索地址人口统计数据。 我相信我需要将查询 #2 插入到查询 #1 中，以便它是一个引用的子查询。 查询 #2 从名为 ADDRESS_EFF_DATE 的表中选择数据，该表存储地址人口统计信息的记录。它有员工每次更改其地址时的记录，EFF_DATE列显示记录的日期。 我需要提取最新的数据，从而提取子查询。 我不知道该怎么做。

查询 #1

SELECT cd.ONECODE as "Client One Code"
,cs.REAL_SSN as "Employee SSN"
,cs.REAL_SSN as "Member SSN"
,'Subscriber' as "Person Type"
,eed.LAST_NAME as "Last Name"
,eed.FIRST_NAME as "First Name"
,eed.BIRTHDATE as MemberDOB
,eed.SEX as MemberGender
,aed.Address_1 as AddressLine1
,aed.Address_2 as AddressLine2
,aed.City
,aed.State
,aed.Zip as ZipCode
,aed.Country as CountryCode
,aed.Telephone as HomeNumber
,eed.EMAIL_ADDRESS as "Employee Emaill Address"
,eed.EFF_DATE as "Date Last Actively at Work"
,eed.LATEST_HIRE_DATE as "Employee Date of Hire"
,eed.LAST_TERM_DATE as "Employee Date of Termination"
,mcemd.RATE_1 as "Employee Salary"
/*,MV_COMB_EMP_MAX_DTS.RATE_1 as "Employee Salary"*/
FROM
EMPLOYEE_EFF_DATE eed
INNER JOIN ADDRESS_EFF_DATE aed 
ON eed.ssn = aed.ssn
INNER JOIN COMP_SSN cs
ON cs.ssn = eed.ssn
INNER JOIN COMPANY_DETAIL cd
ON eed.P_COMPANY_ID_I = cd.COMPANY_ID
INNER JOIN COMPANY_EMPLOYMENT_DATA ced
ON eed.SSN = ced.SSN
INNER JOIN MV_COMB_EMP_MAX_DTS mcemd
ON eed.SSN = mcemd.SSN
WHERE eed.P_COMPANY_ID_I = 4029
AND aed.ADDRESS_KEY = 0

查询 #2

SELECT aed.EFF_DATE
,aed.SSN
,aed.ADDRESS_KEY
,aed.Address_1 as AddressLine1
,aed.Address_2 as AddressLine2
,aed.City
,aed.State
,aed.Zip as ZipCode
,aed.Country as CountryCode
,aed.Telephone as HomeNumber
FROM ADDRESS_EFF_DATE aed
INNER JOIN (
SELECT SSN, ADDRESS_KEY, MAX(EFF_DATE) Eff_Date
FROM ADDRESS_EFF_DATE
GROUP BY SSN, ADDRESS_KEY
) aed2 ON aed.SSN = aed2.SSN AND aed.EFF_DATE = aed2.Eff_Date

Answer 1

最简单(懒惰(的方法是将这两个查询连接为"表表达式"。例如：

select
x.col1,
y.col2
from (
-- query #1 here
) x
join (
-- query #2 here
) y 
on x.col1 = y.col2 -- join predicate here

如果您愿意，也可以使用 CTE。

或者，您可以尝试将两个查询合并为一个查询;这可能会带来更好的性能，但在编码和调试方面需要做更多的工作。

Answer 2

由于您在第一个查询中ADDRESS_EFF_DATE，并且想要获取员工移动的最后一个地址，因此您可以使用 DENSE_RANK 在一个查询中执行此操作。我建议像这样实现它：

SELECT a."Client One Code",
a."Employee SSN" ,
a."Member SSN",
a."Person Type",
a."Last Name",
a."First Name",
a.MemberDOB,
a.MemberGender,
a.AddressLine1,
a.AddressLine2,
a.City,
a.State,
a.ZipCode,
a.CountryCode,
a.HomeNumber,
a."Employee Emaill Address",
a."Date Last Actively at Work",
a."Employee Date of Hire",
a."Employee Date of Termination",
a."Employee Salary" FROM (
SELECT cd.ONECODE as "Client One Code"
,cs.REAL_SSN as "Employee SSN"
,cs.REAL_SSN as "Member SSN"
,'Subscriber' as "Person Type"
,eed.LAST_NAME as "Last Name"
,eed.FIRST_NAME as "First Name"
,eed.BIRTHDATE as MemberDOB
,eed.SEX as MemberGender
,aed.Address_1 as AddressLine1
,aed.Address_2 as AddressLine2
,aed.City
,aed.State
,aed.Zip as ZipCode
,aed.Country as CountryCode
,aed.Telephone as HomeNumber
,eed.EMAIL_ADDRESS as "Employee Emaill Address"
,eed.EFF_DATE as "Date Last Actively at Work"
,eed.LATEST_HIRE_DATE as "Employee Date of Hire"
,eed.LAST_TERM_DATE as "Employee Date of Termination"
,mcemd.RATE_1 as "Employee Salary"
,DENSE_RANK()  OVER (PARTITION BY eed.ssn ORDER BY aed.EFF_DATE DESC) AS drank
/*,MV_COMB_EMP_MAX_DTS.RATE_1 as "Employee Salary"*/
FROM
EMPLOYEE_EFF_DATE eed
INNER JOIN ADDRESS_EFF_DATE aed 
ON eed.ssn = aed.ssn
INNER JOIN COMP_SSN cs
ON cs.ssn = eed.ssn
INNER JOIN COMPANY_DETAIL cd
ON eed.P_COMPANY_ID_I = cd.COMPANY_ID
INNER JOIN COMPANY_EMPLOYMENT_DATA ced
ON eed.SSN = ced.SSN
INNER JOIN MV_COMB_EMP_MAX_DTS mcemd
ON eed.SSN = mcemd.SSN
WHERE eed.P_COMPANY_ID_I = 4029
AND aed.ADDRESS_KEY = 0) a 
WHERE drank=1