如何在 Oracle 10 中连接两个表的结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1560018/
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
How to join results from two tables in Oracle 10
提问by Romain Linsolas
Let say that I have 2 tables with the same structure : STOCKand NEW_STOCK.
These tables have a primary key composed of (ID_DATE, ID_SELLER, ID_INVOICE, ID_DOC).
假设我有 2 个具有相同结构的表 :STOCK和NEW_STOCK. 这些表有一个由 (ID_DATE, ID_SELLER, ID_INVOICE, ID_DOC) 组成的主键。
Now, I need to get for every (ID_DATE, ID_SELLER, ID_INVOICE, ID_DOC), the value of the amount (field AMOUNT) regarding this requirement:
现在，我需要为每个 (ID_DATE, ID_SELLER, ID_INVOICE, ID_DOC) 获取有关此要求的金额（字段 AMOUNT）的值：
If a record is present in NEW_STOCK, I get the AMOUNT from NEW_STOCK, otherwise, I get the AMOUNT from STOCKtable.
如果 中存在记录NEW_STOCK，则从 中获取 AMOUNT NEW_STOCK，否则从STOCK表中获取 AMOUNT 。
Note that ID_DATE and ID_SELLER are the inputs given to the query, i.e. a query that considers only STOCKtable will look like :
请注意， ID_DATE 和 ID_SELLER 是提供给查询的输入，即仅考虑STOCK表的查询将如下所示：
select AMOUNT, ID_DATE, ID_SELLER, ID_INVOICE
from STOCK
where ID_DATE = 1
and ID_SELLER = 'SELL1';
STOCK:
STOCK：
+---------+-----------+------------+--------+--------+
| ID_DATE | ID_SELLER | ID_INVOICE | ID_DOC | AMOUNT |
+---------+-----------+------------+--------+--------+
| 1 | SELL1 | IN1 | DOC1 | 100 |
| 1 | SELL1 | IN2 | DOC2 | 50 |
| 1 | SELL1 | IN3 | DOC3 | 42 |
+---------+-----------+------------+--------+--------+
NEW_STOCK:
NEW_STOCK：
+---------+-----------+------------+--------+--------+
| ID_DATE | ID_SELLER | ID_INVOICE | ID_DOC | AMOUNT |
+---------+-----------+------------+--------+--------+
| 1 | SELL1 | IN2 | DOC2 | 12 |
+---------+-----------+------------+--------+--------+
Then, I must get the following results:
然后，我必须得到以下结果：
+---------+-----------+------------+--------+--------+
| ID_DATE | ID_SELLER | ID_INVOICE | ID_DOC | AMOUNT |
+---------+-----------+------------+--------+--------+
| 1 | SELL1 | IN1 | DOC1 | 100 |
| 1 | SELL2 | IN2 | DOC2 | 12 |
| 1 | SELL3 | IN3 | DOC3 | 42 |
+---------+-----------+------------+--------+--------+
ps: I'm working on Oracle 10.
ps：我正在开发 Oracle 10。
回答by zendar
Use outer join and NVL(arg1, arg2) function. It returns first argument if it is not NULL, otherwise it returns second argument. Example:
使用外连接和 NVL(arg1, arg2) 函数。如果它不为 NULL，则返回第一个参数，否则返回第二个参数。例子：
select s.AMOUNT, s.ID_DATE, s.ID_SELLER, s.ID_INVOICE,
NVL(n.AMOUNT, s.AMOUNT) amount
from STOCK s, NEW_STOCK n
where s.ID_DATE = n.ID_DATE(+)
and s.ID_SELLER = n.ID_SELLER(+)
and s.ID_INVOICE = n.ID_INVOICE(+)
and s.ID_DOC = n.ID_DOC(+)
and s.ID_DATE = 1
and s.ID_SELLER = 'SELL1';
You can use LEFT OUTER JOINsyntax instead of (+)if you find it more readable. I'm using Oracle since v7 and I like (+)more.
Here is LEFT OUTER JOINsyntax:
如果您觉得它更具可读性，您可以使用LEFT OUTER JOIN语法代替(+)。我从 v7 开始使用 Oracle，我喜欢(+)更多。
这是LEFT OUTER JOIN语法：
select s.AMOUNT, s.ID_DATE, s.ID_SELLER, s.ID_INVOICE,
NVL(n.AMOUNT, s.AMOUNT) amount
from STOCK s left outer join NEW_STOCK n
on s.ID_DATE = n.ID_DATE
and s.ID_SELLER = n.ID_SELLER
and s.ID_INVOICE = n.ID_INVOICE
and s.ID_DOC = n.ID_DOC
where s.ID_DATE = 1
and s.ID_SELLER = 'SELL1';
回答by Victor Vostrikov
SELECT * FROM (
SELECT * FROM new_stock
UNION ALL
SELECT * FROM stock
WHERE (ID_DATE,ID_SELLER,ID_INVOICE,ID_DOC) NOT IN
(SELECT ID_DATE,ID_SELLER,ID_INVOICE,ID_DOC FROM new_stock)
)
WHERE ID_DATE = 1
AND ID_SELLER = 'SELL1';
回答by Jonathan Kehayias
The following should work for it:
以下应该适用于它：
SELECT s.AMOUNT, s.ID_DATE, s.ID_SELLER, s.ID_INVOICE
FROM STOCK s
LEFT JOIN NEW_STOCK ns
ON s.ID_DATE = ns.ID_DATE
AND s.ID_SELLER = ns.ID_SELLER
AND s.ID_INVOICE = ns.ID_INVOICE
WHERE s.ID_DATE = 1
AND s.ID_SELLER = 'SELL1'
AND ns.ID_DATE IS NULL
UNION
SELECT AMOUNT, ID_DATE, ID_SELLER, ID_INVOICE
FROM NEW_STOCK
WHERE ID_DATE = 1
AND ID_SELLER = 'SELL1';
Exclude the matched rows from a LEFT JOIN and UNION that set with the results from the NEW_STOCK table.
从使用 NEW_STOCK 表的结果设置的 LEFT JOIN 和 UNION 中排除匹配的行。
回答by manji
SELECT COALESCE(NS.AMOUNT, S.AMOUNT) AMOUNT,
S.ID_DATE,
S.ID_SELLER,
S.ID_INVOICE
FROM STOCK S
LEFT JOIN NEW_STOCK NS ON S.ID_DATE = NS.ID_DATE
AND S.ID_SELLER = NS.ID_SELLER
AND S.ID_INVOICE = NS.ID_INVOICE
AND S.ID_DOC = NS.ID_DOC
WHERE S.ID_DATE = 1
AND S.ID_SELLER = 'SELL1'
