如何仅在JOIN条件返回匹配记录的情况下应用RANK()

Question

我正在处理一个Oracle SQL查询，在该查询中，我只想在有匹配记录的情况下应用列组。这是我的数据：-

Table 1:
ID State_Code
1   NY
2   DC
3   AL
Table 2:
ID EXPIRY_DATE STATE_CODE X_CODE
1   30-DEC-21     NY        ABC
1   30-JUN-21     NY        XYZ
2   30-DEC-21     DC        SQL
2   30-JUN-21     AZ        DEF
3   30-JUN-21     AK        PQR
RESULT:
ID STATE_CODE  X_CODE
1    NY         ABC
2    DC         SQL
3    AL         (null)

我想根据ID加入状态代码。如果有多个匹配，则根据Expiry_Dates选择/排列记录。

Answer 1

如果您想要两个表中的列，那么这是一个方便的横向连接位置。根据你的描述，你想要：

select t1.*, t2.x_code
from table1 t1 cross join lateral
(select t2.*
from table2 t2
where t2.id = t1.id
order by expiry_date desc
fetch first 1 row only
) t2;

根据您的样本数据，您似乎希望id和state_code:都匹配

select t1.*, t2.x_code
from table1 t1 left join lateral
(select t2.*
from table2 t2
where t2.id = t1.id and t2.state_code = t1.state_code
order by expiry_date desc
fetch first 1 row only
) t2
on 1=1;

这里有一个db<gt；不停摆弄

Answer 2

你可以这样做：

WITH table1 (id, state_code) AS
(
SELECT 1, 'NY' FROM DUAL UNION ALL
SELECT 2, 'DC' FROM DUAL UNION ALL
SELECT 3, 'AL' FROM DUAL 
), table2 (id,expiry_date,state_code,x_code)
AS
(
SELECT 1, TO_DATE('30-DEC-21','DD-MON-YY'), 'NY','ABC' FROM DUAL UNION ALL
SELECT 1, TO_DATE('30-JUN-21','DD-MON-YY'), 'NY','XYZ' FROM DUAL UNION ALL
SELECT 2, TO_DATE('30-DEC-21','DD-MON-YY'), 'DC','SQL' FROM DUAL UNION ALL
SELECT 2, TO_DATE('30-JUN-21','DD-MON-YY'), 'AZ','DEF' FROM DUAL UNION ALL
SELECT 3, TO_DATE('30-JUN-21','DD-MON-YY'), 'AK','PQR' FROM DUAL 
), table2_ranked (id,expiry_date,state_code,x_code, rnk) AS
(
SELECT id,expiry_date,state_code,x_code, RANK() OVER (ORDER BY expiry_date DESC) 
FROM table2
)
SELECT t1.id, t1.state_code, t2.x_code
FROM table1 t1 
LEFT OUTER JOIN table2_ranked t2 ON t1.state_code = t2.state_code AND t2.rnk = 1

Answer 3

开始：

Select * from (
Select a.ID,a.State_Code,b.X_CODE ,
row_number() over(partition by a.id,a.state_code order by TO_DATE(b.expiry_date,'DD-MON-YY') desc) as rnum 
from Tab1 a left join tab2 b on a.id = b.id and a.state_code = b.state_code
) c where rnum = 1