主页 / dba / 问题 / 269609
Accepted
Kathir E
Asked: 2020-06-23 01:00:33 +0800 CST

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

  • 772

表详细信息：

CREATE TABLE Test2 (
  ID INT,
  Value INT,
  other INT);
  
CREATE TABLE Test1 (
  ID INT,
  TYPE INT,
  other INT);
  
INSERT INTO Test2 VALUES (123456, 5, 12);


INSERT INTO Test1 VALUES (123456, 00, 2);
INSERT INTO Test1 VALUES (123456, 01, 6);
INSERT INTO Test1 VALUES (123456, 02, 4);

INSERT INTO Test1 VALUES (987654, 00, 7);
INSERT INTO Test1 VALUES (987654, 01, 8);

INSERT INTO Test1 VALUES (456789, 00, 6);
INSERT INTO Test1 VALUES (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 BY t1.ID ASC;

查询结果：

ID  TYPE    other   value
123456  0   2   5
123456  1   6   5
123456  2   4   5

描述：

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

预期的查询结果是，

ID  TYPE    other   value
123456  0   2   5
123456  1   6   
123456  2   4

SQL小提琴链接

http://sqlfiddle.com/#!9/8953fc/29

Criteria 当有 ID 匹配时，应该从左表（Test1）和右表（Test2）中获取所有匹配的记录，应该得到不同的记录。

oracle oracle-11g

1 个回答

  1. Best Answer

    在客户端（sqlplus）执行它：

    SQL> 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 BY t1.ID ASC;

        ID       TYPE      OTHER      VALUE
---------- ---------- ---------- ----------
    123456          0          2          5
    123456          1          6          5
    123456          2          4          5

SQL> break on value
SQL> 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 BY t1.ID ASC;

        ID       TYPE      OTHER      VALUE
---------- ---------- ---------- ----------
    123456          0          2          5
    123456          1          6
    123456          2          4

    在 SQL 中：

    SQL> clear breaks
breaks cleared
SQL> 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 BY t1.ID ASC;

        ID       TYPE      OTHER      VALUE
---------- ---------- ---------- ----------
    123456          0          2          5
    123456          1          6          5
    123456          2          4          5

SQL> SELECT DISTINCT t1.ID, t1.TYPE, t1.other, case when row_number() over (partition by t1.id order by t1.id) = 1 then t2.value end value FROM Test1 t1 INNER JOIN Test2 t2 ON t1.ID = t2.ID GROUP BY t1.ID, t1.TYPE, t1.other, t2.value ORDER BY t1.ID ASC;

        ID       TYPE      OTHER      VALUE
---------- ---------- ---------- ----------
    123456          0          2          5
    123456          1          6
    123456          2          4
    • 1

相关问题