从表2返回来自表1上不同ID的相同名称

Question

我一直在试图开发一个查询来解决问题，但它很难。

表1：

+------+----+ 
| NAME | ID | 
+------+----+ 
| A | 1 | 
| A | 2 | 
| B | 1 | 
| B | 5 | 
| C | 8 | 
+------+----+ 

表2：

+------+----+ 
| NAME | ID | 
+------+----+ 
| A | 1 | 
| A | 4 | 
| B | 3 | 
| B | 5 | 
| D | 9 | 
+------+----+ 

从这些结果，我需要从表2的名称表1中包含与ID不返回的一切。

所以，这个例子中，回报应该是：

+------+----+ 
| NAME | ID | 
+------+----+ 
| A | 4 | 
| B | 3 | 
+------+----+ 

Answer 1

你可能想试试这个：

编辑：用WITH子句中的简单子查询替换table1和table2。

WITH table1 AS 
(
SELECT 
    DECODE(LEVEL,1, 'A',2, 'A',3, 'B',4, 'B',5, 'C') AS name 
    ,DECODE(LEVEL,1, 1,2, 2,3, 1,4, 5,5, 8) AS id 
FROM 
    dual 
CONNECT BY LEVEL < 6 
) 
,table2 AS 
(
SELECT 
    DECODE(LEVEL,1, 'A',2, 'A',3, 'B',4, 'B',5, 'D') AS name 
    ,DECODE(LEVEL,1, 1,2, 4,3, 3,4, 5,5, 9) AS id 
FROM 
    dual 
CONNECT BY LEVEL < 6 
) 
SELECT 
    t2.id 
    ,t2.name 
FROM 
    table1 t1 
    ,table2 t2 
WHERE 
    t1.name = t2.name -- here we take all the records from table2, which have the same names as in table1 
MINUS -- then we "subtract" the records that have both the same name and id in both tables 
SELECT 
    t2.id 
    ,t2.name 
FROM 
    table1 t1 
    ,table2 t2 
WHERE 
    t1.name = t2.name 
    AND t1.id = t2.id 

Answer 2

您可以使用NOT EXISTS或类似：

SELECT t2.* 
FROM Table2 t2 
WHERE NOT EXISTS 
(
    SELECT 1 
    FROM Tabl1 t1 
    WHERE t1.Name = t2.Name 
    AND t1.Id = t2.Id 
); 

Answer 3

SELECT T1.ID,T1.NAME 
FROM TABLE2 T1 INNER JOIN TABLE1 T2 ON T1.NAME = T2.NAME 
       LEFT JOIN TABLE1 T3 ON T3.ID = T1.ID 
WHERE T3.ID IS NULL 

Answer 4

我会做：

with t1 as (select 'A' name, 1 id from dual union all 
      select 'A' name, 2 id from dual union all 
      select 'B' name, 1 id from dual union all 
      select 'B' name, 5 id from dual union all 
      select 'C' name, 8 id from dual), 
    t2 as (select 'A' name, 1 id from dual union all 
      select 'A' name, 4 id from dual union all 
      select 'B' name, 3 id from dual union all 
      select 'B' name, 5 id from dual union all 
      select 'D' name, 9 id from dual) 
select name, id 
from t2 
where name in (select name from t1) 
minus 
select name, id 
from t1; 

NAME   ID 
---- ---------- 
A    4 
B    3