Oracle sql 查询联合操作？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15245880/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle sql query union operation?
提问by user1016403
I have two tables. TableAand TableB. both the tables has some data with two columns as below.
我有两张桌子。TableA和TableB。这两个表都有一些包含两列的数据，如下所示。
TableA
---------
id Name
--- ----
1 abc
2 def
TableB
---------
id Name
--- ----
1 xyz
2 pqr
Now i would pass list of ids from my application and get same ids along with their names as:
现在，我将从我的应用程序中传递 id 列表，并获得相同的 id 及其名称：
select id, name
from TableA
where id in(1,2)
union select id, name
from TableB
where id in(1,2);
above query gives results as:
上面的查询给出的结果为：
1 abc
1 xyz
2 def
2 pqr
But what i need is if same id is present in both the tables then TableB's Name should be considered but not TableA's name.
但是我需要的是，如果两个表中都存在相同的 ID，则应考虑 TableB 的名称，而不考虑 TableA 的名称。
Expected output:
1 xyz
2 pqr
One more is, if TableB does not contain any data then TableA's data should be fetched.
还有一种情况是，如果 TableB 不包含任何数据，则应获取 TableA 的数据。
How can i do that?
我怎样才能做到这一点？
Thanks!
谢谢！
回答by TechDo
Please try using LEFT JOIN.
请尝试使用 LEFT JOIN。
SELECT TableA.ID,
NVL(TableB.Name, TableA.Name) Name FROM
TableA LEFT JOIN TableB
ON TableA.ID=TableB.ID
WHERE TableA.ID IN (1, 2)
回答by Meherzad
Try this query using simple union you can club the records
使用简单的联合试试这个查询，你可以把记录放在一起
SELECT id, name from tableA where id not in (SELECT id FROM tableB)
UNION ALL
SELECT id, name from tableB
回答by echo_Me
try this
尝试这个
SELECT id , name from (
select id, name from TableA where id in(1,2)
union select id, name from TableB where id in(1,2)) t
GROUP BY id;
回答by www
Try this:
尝试这个：
select id, name from TableA where id in(1,2) and id not in ( select id from TableB) a union select id, name from TableB where id in(1,2);
回答by David Aldridge
Use Union All, and exists/not exists to control which results are returned from table_a based on the existence of any records in table_b
使用Union All，以及exists/not exists，根据table_b中是否存在任何记录来控制从table_a返回哪些结果
select id,name
from (
select id,name
from table_b
union all
select id,name
from table_a
where exists (select null from table_b) and
not exists (
select null
from table_b
where table_b.id = table_a.id)
union all
select id,name
from table_a
where not exists (select null from table_b))
where id in (1,2)
回答by DazzaL
one way of doing it if rows can be in A, A+B or B is (if tableaalways has data, then techdo's answer is better):
如果行可以在 A、A+B 或 B 中，一种方法是（如果tablea总是有数据，那么 techdo 的答案更好）：
select id, name
from (select id, name, row_number() over (partition by id order by rnk) rn
from (select id, name, 1 rnk from tableb
union all
select id, name, 2 rnk from tablea))
where rn = 1;
回答by Avadhesh
SELECT (case when B.id = A.id then b.id else a.id end) as id,
(case when B.id = A.id then b.name else a.name end) as name
FROM tableA a left JOIN tableB b ON (a.id = b.id)
回答by Art
MINUS operator - returns only unique rows returned by the first query but not by the second:
MINUS 运算符 - 仅返回第一个查询返回的唯一行，但不返回第二个查询：
WITH tab_a AS
(
SELECT 1 id, 'abc' val FROM dual
UNION
SELECT 2, 'def' FROM dual
),
tab_b AS
(
SELECT 1, 'xyz' val FROM dual
UNION
SELECT 2, 'pqr' FROM dual
)
-- This is your query --
SELECT * FROM tab_b
MINUS
SELECT * FROM tab_a
/
ID VAL
----------
1 xyz
2 pqr
回答by r_hudson
Following code is for selecting data from both the tables(A +B) and then taking out data using minus and join for the rows not required. Code can be easily modified if the requirement changes from selecting names from table A instead of table B.
以下代码用于从两个表（A + B）中选择数据，然后对不需要的行使用减号和连接取出数据。如果从表 A 而不是表 B 中选择名称，要求发生变化，则可以轻松修改代码。
select * from tableA where id in (1,2)
union
select * from tableB where id in (1,4)
minus
select a,id, a.Name from tableA a join tableB b on a.id = b.id where
a.id in (1,2);
