连接两个具有不同详细程度的表，并删除重复的值

Question

我有两张表：

Table1 (ID1，ID2，PERIOD，VALUE1)

WITH Table1 (ID1, ID2, PERIOD, VALUE1) AS (
SELECT 1,   'A', '202101',   100  FROM DUAL UNION ALL
SELECT 1,   'A', '202102',   200  FROM DUAL UNION ALL
SELECT 1,   'A', '202103',   300  FROM DUAL UNION ALL
SELECT 1,   'A', '202104',   400  FROM DUAL UNION ALL
SELECT 2,   'A', '202101',   500  FROM DUAL UNION ALL
SELECT 2,   'A', '202102',   600  FROM DUAL UNION ALL
SELECT 2,   'A', '202103',   700  FROM DUAL UNION ALL
SELECT 2,   'A', '202104',   800  FROM DUAL 

Table2 (ID2，PERIOD，VALUE2)

WITH Table2 (ID2, PERIOD, VALUE2) AS (
SELECT 'A', '202101',   11  FROM DUAL UNION ALL
SELECT 'A', '202102',   12  FROM DUAL UNION ALL
SELECT 'A', '202103',   13  FROM DUAL UNION ALL
SELECT 'A', '202104',   14  FROM DUAL 

in没有按顺序排列，并且不是递增的。我想在ID2上连接这两个表，并且在给定的时间段内只获取一次Value2值。如何替换具有相同ID2的多个ID1条目的所有重复项？

我当前的输出如下所示：

ID1   ID2   PERIOD    Value1   Value2
-------------------------------------
1     A     202101    100      11
1     A     202102    200      22
1     A     202103    300      33
1     A     202104    400      44
2     A     202101    500      11
2     A     202102    600      22
2     A     202103    700      33
2     A     202104    800      44

我想要的输出将如下所示：

ID1   ID2   PERIOD    Value1   Value2
-------------------------------------
1     A     202101    100      11
1     A     202102    200      22
1     A     202103    300      33
1     A     202104    400      44
2     A     202101    500      null
2     A     202102    600      null
2     A     202103    700      null
2     A     202104    800      null

Answer 1

嗯，假设id1定义了行上的相关顺序，一种方法是在ON子句中使用NOT EXISTS检查给定的id1是否存在较低的period。

SELECT t11.id1,
       t11.id2,
       t11.period,
       t11.value1,
       t21.value2
       FROM table1 t11
            LEFT JOIN table2 t21
                      ON t21.id2 = t11.id2
                         AND t21.period = t11.period
                         AND NOT EXISTS (SELECT *
                                                FROM table1 t12
                                                WHERE t12.period = t11.period
                                                      AND t12.id1 < t11.id1);

另一种类似的方法是使用相关子查询来获取该时间段的最小id1。

SELECT t11.id1,
       t11.id2,
       t11.period,
       t11.value1,
       t21.value2
       FROM table1 t11
            LEFT JOIN table2 t21
                      ON t21.id2 = t11.id2
                         AND t21.period = t11.period
                         AND t11.id1 = (SELECT min(t12.id1)
                                               FROM table1 t12
                                               WHERE t12.period = t11.period);

db<>fiddle

看看哪一个在您的环境中表现得更好。

Answer 2

您可以使用窗口函数枚举值。然后将其包含在join中

select t1.*, t2.*
from (select t1.*,
             row_Number() over (partition by id2, period order by id1) as seqnum
      from table1 t1
     ) t1 left join
     table2 t2
     on t2.id2 = t1.id2 and t2.period = t1.period and t1.seqnum = 1;

Here是一个db<>fiddle。