主页 / dba / 问题 / 269635
Accepted
Kathir E
Asked: 2020-06-23 05:30:42 +0800 CST

如何在不从 Oracle 中的左右表中获取重复的情况下加入两个表

  • 772

表详细信息：

CREATE TABLE Test2 (ID INT, Value INT, other INT);

创建表Test1（ID INT，TYPE INT，其他INT）；

插入测试2值（123456、5、12）；插入测试2值（123456、10、17）；

插入测试 1 值 (123456, 00, 2); 插入测试 1 值 (123456, 01, 6); 插入测试 1 值 (123456, 02, 4);

插入测试 1 值 (987654, 00, 7); 插入测试 1 值 (987654, 01, 8);

插入测试 1 值 (456789, 00, 6); 插入测试 1 值 (456789, 01, 16);

这是我用来避免表测试重复的查询

SELECT DISTINCT t1.ID, t1.TYPE, t1.other, t2.value
FROM Test1 t1 INNER JOIN Test2 t2 ON t1.ID = t2.ID GROUP BY t1.ID, t1.TYPE, t1.other, t2.value ORDER通过 t1.ID ASC;

查询结果：

ID TYPE 其他值 123456 0 2 5 123456 0 2 10 123456 1 6 5 123456 1 6 10 123456 2 4 5 123456 2 4 10

描述：

预期结果是，当我从表中获取匹配记录时。应该从左表（Test1）中获取没有重复记录的记录，并避免从右表（Test2）中获取重复记录。

参考检查图片

预期的查询结果是，

23456 0 2 5 123456 1 6 10 123456 2 4

SQL小提琴链接

http://sqlfiddle.com/#!9/3cd8a0/26

Criteria 当有ID匹配时，应该从左表（Test1）中获取匹配记录，不重复，右表（Test2）不重复。 在此处输入图像描述

oracle oracle-11g-r2

1 个回答

  1. Best Answer

    尽管您可以在它们的 ID 上加入 2 个表，但您可能需要引入“人工”行号（实际上），并在您的连接中使用它们。

    表格和数据

    select * from test1 ;
+------+----+-----+
|ID    |TYPE|OTHER|
+------+----+-----+
|123456|0   |2    |
|123456|1   |6    |
|123456|2   |4    |
|987654|0   |7    |
|987654|1   |8    |
|456789|0   |6    |
|456789|1   |16   |
+------+----+-----+


select * from test2 ;
+------+-----+-----+
|ID    |VALUE|OTHER|
+------+-----+-----+
|123456|5    |12   |
|123456|10   |17   |
+------+-----+-----+

    SELECT 和 ROW_NUMBER()

      select id, type, other
  , row_number() over ( partition by id order by type ) rn_
  from test1 ;

+------+----+-----+---+
|ID    |TYPE|OTHER|RN_|
+------+----+-----+---+
|123456|0   |2    |1  |
|123456|1   |6    |2  |
|123456|2   |4    |3  |
|456789|0   |6    |1  |
|456789|1   |16   |2  |
|987654|0   |7    |1  |
|987654|1   |8    |2  |
+------+----+-----+---+

  select id, value, other
  , row_number() over ( partition by id order by value ) rn_
  from test2 ;

+------+-----+-----+---+
|ID    |VALUE|OTHER|RN_|
+------+-----+-----+---+
|123456|5    |12   |1  |
|123456|10   |17   |2  |
+------+-----+-----+---+

    如果您现在加入这两个结果集，您可能更接近您所追求的解决方案。

    select T1.id, T1.type, T1.other, T2.value
from (
  select id, type, other
  , row_number() over ( partition by id order by type ) rn_
  from test1
) T1 left join (
  select id, value, other
  , row_number() over ( partition by id order by value ) rn_
  from test2
) T2 on T1.id = T2.id and T1.rn_ = T2.rn_ ;

+------+----+-----+-----+
|ID    |TYPE|OTHER|VALUE|
+------+----+-----+-----+
|123456|0   |2    |5    |
|123456|1   |6    |10   |
|456789|0   |6    |NULL |
|456789|1   |16   |NULL |
|987654|0   |7    |NULL |
|987654|1   |8    |NULL |
|123456|2   |4    |NULL |
+------+----+-----+-----+

    现在您只需要消除 ID不在Table2 中的行。

    -- final query
select T1.id, T1.type, T1.other, T2.value
from (
  select id, type, other
  , row_number() over ( partition by id order by type ) rn_
  from test1
) T1 left join (
  select id, value, other
  , row_number() over ( partition by id order by value ) rn_
  from test2
) T2 on T1.id = T2.id and T1.rn_ = T2.rn_
where exists ( select null from test2 where id = T1.id )
order by 1, 2 ;

+------+----+-----+-----+
|ID    |TYPE|OTHER|VALUE|
+------+----+-----+-----+
|123456|0   |2    |5    |
|123456|1   |6    |10   |
|123456|2   |4    |NULL |
+------+----+-----+-----+

    DBfiddle (Oracle 18c) SQLfiddle (Oracle 11g)

    • 0

相关问题