如何合并两个表以从表2中获取最后的新行，并从表1中获取其余的行 [英] how to merge two tables to get the last new rows from table 2 and the rest from table 1

问题描述

我在将两个表连接到oracle数据库时遇到问题，但我无法做到这一点. 如果您在图像上看到，可以有两个选择.

i have a problem with joins on two tables into an oracle database, and i can't do that it works. if you see on the image you can two options.

我执行此查询:

SELECT f.id_hist, f.producto, f.price
FROM TABLE(fnc_historical('JAP')) f
    inner join new_table g on (f.id_new <> g.id_hist)
union
SELECT f.id_hist, f.producto, g.new_price
FROM TABLE(fnc_historical('JAP')) f
    inner join new_table g on (f.id_new = g.id_hist)

对于选项/案例1，它有效！

for option/case 1 it works!

但对于选项/情况2，此查询返回空值，无行.其想法是必须从历史信息中返回所有行.

but for option/case 2 this query return empty, no rows. and the idea is that must resturn all row from historical information.

有人可以帮我吗?

非常感谢.

推荐答案

只需使用NOT IN查找与new_table g不匹配的记录，并收集由INNER JOIN

Just use NOT IN to find the records unmatched with new_table g, and gather with the records retrieved by INNER JOIN

SELECT f.id_hist, f.producto, f.price
FROM TABLE(fnc_historical('JAP')) f
WHERE f.id_hist NOT IN
(SELECT DISTINCT f.id_hist
FROM TABLE(fnc_historical('JAP')) f
    inner join new_table g on (f.id_new = g.id_hist)
)
UNION
SELECT f.id_hist, f.producto, g.new_price
FROM TABLE(fnc_historical('JAP')) f
    inner join new_table g on (f.id_new = g.id_hist)

这篇关于如何合并两个表以从表2中获取最后的新行，并从表1中获取其余的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！