在 Oracle sql 中连接两个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8298529/
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
Joining two tables in Oracle sql
提问by mkab
I have 2 tables. One of the tables have 7 values and the other table has 5 values. These tables have their primary keys in common. I want to join both tables this way: If I have a Table
我有2张桌子。其中一个表有 7 个值，另一个表有 5 个值。这些表具有共同的主键。我想以这种方式连接两个表：如果我有一个表
English French
-------------------- --------------------
one Un
two Deux
three Trois
four Quatre
four Quattro
five Cinq
five Cinco
And another one:
还有一个：
English French
-------------------- --------------------
one aaaaa
two bbbbb
three ccccc
four
five
I want to have a table like this:
我想要一张这样的桌子：
English French
-------------------- --------------------
one Un
one aaaaa
two Deux
two bbbb
three Trois
three ccccc
four Quatre
four Quattro
four --------
five Cinq
five Cinco
five ----------
I tried using join but it does a linear combination of the values fourand five. How can I go about doing this? Thanks.
我尝试使用 join 但它对值four和five. 我该怎么做呢？谢谢。
Edit: SQL query:
编辑：SQL 查询：
SELECT l.date_location, l.duree, r.km_restitution, r.km_parcouru
FROM locations l, restitutions r
UNION
SELECT l.num_client, l.date_location, l.duree, r.km_restitution, r.km_parcouru
FROM locations l, restitutions r
id_agence num_immatriculation num_client km_restitution km_parcouru state date_restitution
1 406BON69 1002 30000 1000 BON 29-MAY-10
3 785CIM13 1001 56580 80 BON 09-AUG-08
5 800BBB75 1000 2020 20 BON 24-APR-11
4 307VXN78 1000 20040 40 BON 28-JAN-11
2 290UTT92 1004 30030 30 BON 01-AUG-10
5 777SET13 1005 4030 30 BON 26-APR-11
2 179CLV92 1004 15015 15 BON 03-FEB-11
5 400AAA75 1003 1020 20 BON 18-SEP-11
5 666NEF69 1004 3040 40 BON 15-APR-11
2 111AAA75 1001 20020 20 BON 21-DEC-09
1 333CCC78 1001 43250 40 BON 27-DEC-09
2 260CDE95 1003 79000 430 BON 10-SEP-09
4 307VXN78 1003 20090 90 BON 11-FEB-11
1 123ABC78 1003 10010 10 BON 04-OCT-10
1 222BBB77 1001 9050 50 BON 23-DEC-09
Locations
id_agence num_immatricul num_client duree date_location
2 406BON69 1002 20 10-MAY-10
3 785CIM13 1001 3 07-AUG-08
5 800BBB75 1000 7 18-APR-11
4 307VXN78 1000 5 24-JAN-11
1 290UTT92 1004 1 31-JUL-10
5 777SET13 1005 4 23-APR-11
1 179CLV92 1004 5 30-JAN-11
5 400AAA75 1003 2 17-SEP-11
2 123ABC78 1003 4 01-OCT-10
5 666NEF69 1004 5 11-APR-11
1 111AAA75 1001 2 20-DEC-09
1 222BBB77 1001 2 22-DEC-09
1 333CCC78 1001 3 25-DEC-09
1 260CDE95 1003 10 01-SEP-09
4 307VXN78 1003 13 30-JAN-11
2 123ABC78 1003 8 20-NOV-11
2 406BON69 1002 10 20-NOV-11
Desired Result
想要的结果
id_agence num_immatricul num_client duree date_location date_restitution
2 406BON69 1002 20 10-MAY-10 date_restitution
3 785CIM13 1001 3 07-AUG-08 date_restitution
5 800BBB75 1000 7 18-APR-11 date_restitution
4 307VXN78 1000 5 24-JAN-11 date_restitution
1 290UTT92 1004 1 31-JUL-10 date_restitution
5 777SET13 1005 4 23-APR-11 date_restitution
1 179CLV92 1004 5 30-JAN-11 date_restitution
5 400AAA75 1003 2 17-SEP-11 date_restitution
2 123ABC78 1003 4 01-OCT-10 date_restitution
5 666NEF69 1004 5 11-APR-11 date_restitution
1 111AAA75 1001 2 20-DEC-09 date_restitution
1 222BBB77 1001 2 22-DEC-09 date_restitution
1 333CCC78 1001 3 25-DEC-09 date_restitution
1 260CDE95 1003 10 01-SEP-09 date_restitution
4 307VXN78 1003 13 30-JAN-11 date_restitution
2 123ABC78 1003 8 20-NOV-11 ----------------
2 406BON69 1002 10 20-NOV-11 ---------------
Apart from the column name, where i put date_restitution contains real dates.
除了列名，我放的 date_restitution 包含真实日期。
回答by
You could use a UNION:
你可以使用联合：
select English, French from Table1
UNION ALL
select English, French from Table2
or a full outer join
或完整的外连接
select distinct coalesce(T1.English, T2.English), coalesce(T1.French, T2.French)
from Table1 T1
full outer join Table2 T2 on T1.English = T2.English
EDIT:
编辑：
Assuming you want restitutions.date_restitution to appear in place of date_location for restitution records -
假设您希望 restitutions.date_restitution 代替 date_location 出现在恢复记录中 -
SELECT l.num_client, l.date_location, l.duree, to_number(null) km_restitution, to_number(null) km_parcouru
FROM locations l
UNION ALL
SELECT r.num_client, r.date_restitution date_location, 0 duree, r.km_restitution, r.km_parcouru
FROM restitutions r
FURTHER EDIT (based on supplied results):
进一步编辑（基于提供的结果）：
select l.id_agence,
l.num_immatricul,
l.num_client,
l.duree,
l.date_location,
decode(r.date_restitution, NULL,'----------------', 'date_restitution')
as date_restitution -- or just r.date_restitution
from location l
left outer join restitution r
on l.id_agence = r.id_agence and
l.num_immatricul = r.num_immatricul and
l.num_client = r.num_client and
l.date_location <= r.date_restitution
回答by vc 74
You actually need a union:
你实际上需要一个工会：
SELECT English, French FROM T1
UNION
SELECT English, French FROM T2
If you don't care about duplicates, you can use UNION ALL
如果你不关心重复，你可以使用 UNION ALL
Edit after OP's comment:
在 OP 评论后编辑：
SELECT l.num_client, l.id_agence, l.num_immatricul
FROM locations l
UNION
SELECT r.num_client, r.id_agence, r.num_immatriculation
FROM restitutions r
回答by OTTA
The following should do it.
下面应该这样做。
SELECT tab1.English, tab1.French
UNION
SELECT tab2.English, tab2.French
回答by mkab
For other readers who might have the same problem. From the experience I had with this problem, it would be a good idea to join tables locations and restitutions since both of them have almost the same attributes and data. I finally decided in changing my database and creating a new table that contains both the attributes of location and restitution and setting some not availabe values to NULL. This would reduce a lot of joinsbetween tables and queries would be easier to handle.
对于可能有同样问题的其他读者。根据我对这个问题的经验，连接表位置和恢复是一个好主意，因为它们具有几乎相同的属性和数据。我最终决定更改我的数据库并创建一个包含位置和恢复属性的新表，并将一些不可用的值设置为NULL. 这会减少很多joins表和查询之间的关系会更容易处理。
