联接表MySQL [英] Join Tables MySQL

问题描述

我有多个表，并做了2个子选择(UserRecord，CustomerRecord)，我希望将它们合并到1个表中

I have multiple tables and made 2 sub-selects (UserRecord,CustomerRecord) that i would like to merge into 1 table

UserRecord
========================
| RecordID | UserName |
========================
| 1        | Sara     |
| 1        | Tom      |
| 2        | Sara     |
| 2        | Kurt     |
| 3        | Fre      |
========================

Table: CustomerRecord
============================
| RecordID | CustomerName |
============================
| 1        | Jef          |
| 2        | Alex         |
| 2        | Peter        |
============================

Table: This should be the result
=======================================
| RecordID | UserName | CustomerName | 
=======================================
| 1        | Sara     | -            |
| 1        | Tom      | -            |
| 1        | -        | Jef          |
| 2        | Sara     | -            |
| 2        | Kurt     | -            |
| 2        | -        | Alex         |
| 2        | -        | Peter        |
| 3        | Fre      | -            |
=======================================

- = null

我尝试用左，右，左外，右外...在2张桌子上合拢，但我没有得到想要的东西.

I tried with left, right, left outer, right outer ... join on the 2 tables but i don't get what i would like.

SELECT *
FROM UserRecord AS ur
INNER JOIN CustomerRecord AS cr ON ur.RecordID = cr.RecordID;

推荐答案

您想要的不是联接，而是UNION:

What you want is not a join, but a UNION:

SELECT RecordID, UserName, NULL AS CustomerName FROM UserRecord
UNION
SELECT RecordID, NULL AS UserName, CustomerName FROM CustomerRecord

...只是追加两个表中的记录.

... which just appends records from the two tables.

我要补充的是，该订单将不会是您在预期结果中显示的订单.如果顺序很重要，则应该从此UNION中进行SELECT并在此外部select上添加一个显式的ORDER BY子句.像这样:

I'd just add that the order will not be the one you have shown in your expected result. If order is important then you should SELECT from this UNION and add an explicit ORDER BY clause on this outer select. Something like:

SELECT * FROM (
    SELECT RecordID, UserName, NULL AS CustomerName FROM UserRecord
    UNION
    SELECT RecordID, NULL AS UserName, CustomerName FROM CustomerRecord
) ORDER BY RecordID, UserName, CustomerName

这篇关于联接表MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！