如何在MySQL中连接具有不同行数的两个表？ [英] How can I join two tables with different number of rows in MySQL?
本文介绍了如何在MySQL中连接具有不同行数的两个表？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有两个表要连接。
TABLE_A：
+ ----------- + ----------- + --------- +
| row_id |类别| val_1 |
+ ----------- + ----------- + --------- +
| 1067 | cat1 | 65 |
| 2666 |测试| 6.5 |
| 2710 | cat1 | 2.1 |
| 2710 |测试| 7.1 |
| 2767 |测试| 3 |
| 71142 | cat1 | 5 |
| 50666 |其他| 6.5 |
| 71142 |其他| 1 |
| 345342 | cat1 | 6.5 |
| 345342 |测试| 2.8 |
+ ----------- + ----------- + --------- +
TABLE_B：
+ ----- ------ + ----------- +
| row_id | val_2 |
+ ----------- + ----------- +
| 1067 | 2.0 |
| 2666 | 9 |
| 2701 | 2.2 |
| 2708 | 1 |
| 2709 | 6.5 |
| 2710 | 5.2 |
| 2765 | 6.5 |
| 2766 | 15 |
| 2767 | 8 |
| 71142 | 5 |
| 2783 | 4.5 |
| 50666 | 6.5 |
| 101588 | 9 |
| 101588 | 3 |
| 3452 | 8.0 |
| 23422 | 5 |
| 345342 | 6.5 |
+ ----------- + ----------- +
RESULT_TABLE：
+ ----------- + --- -------- + ----------- + ------------ +
| row_id | val_2 | val_1 |类别|
+ ----------- + ----------- + ----------- + --------- --- +
| 1067 | 2.0 | 6.5 | cat1 |
| 2666 | 9 | 6.5 |测试|
| 2701 | 2.2 | 2.2 | NULL |
| 2708 | 1 | 1 | NULL |
| 2709 | 6.5 | 1 | NULL |
| 2710 | 5.2 | 2.1 | cat1 |
| 2710 | 5.2 | 7.1 |测试|
| 2765 | 6.5 | 1 | NULL |
| 2766 | 15 | 1 | NULL |
| 2767 | 8 | 3 |测试|
| 71142 | 5 | 5 | cat1 |
| 71142 | 5 | 1 |其他|
| 2783 | 4.5 | 1 | NULL |
| 50666 | 6.5 | 6.5 |其他|
| 101588 | 9 | 1 | NULL |
| 101588 | 3 | 1 | NULL |
| 3452 | 8.0 | 1 | NULL |
| 23422 | 5 | 1 | NULL |
| 345342 | 6.5 | 6.5 | cat1 |
| 345342 | 6.5 | 2.8 |测试|
+ ----------- + ----------- + ----------- + --------- --- +
我试图使用这样的东西：
SELECT TABLE_A.row_id，TABLE_A.category，TABLE_A.val_1，TABLE_B.val_2
FROM TABLE_A
INNER JOIN TABLE_B ON TABLE_B.row_id = TABLE_A .row_id
ORDER BY row_id;
但是，结果仅包含
row_id
有没有办法连接TABLE_A和TABLE_B以产生RESULT_TABLE中显示的结果？
解决方案如果您需要所有结果，则需要外部连接，而不是内部。 （Inner只返回有匹配的行; outer返回所有行，匹配的行拼接在一起）
I have two tables which I want to connect.
TABLE_A:
+-----------+-----------+---------+ | row_id | category | val_1 | +-----------+-----------+---------+ | 1067 | cat1 | 6.5 | | 2666 | test | 6.5 | | 2710 | cat1 | 2.1 | | 2710 | test | 7.1 | | 2767 | test | 3 | | 71142 | cat1 | 5 | | 50666 | other | 6.5 | | 71142 | other | 1 | | 345342 | cat1 | 6.5 | | 345342 | test | 2.8 | +-----------+-----------+---------+
TABLE_B:
+-----------+-----------+ | row_id | val_2 | +-----------+-----------+ | 1067 | 2.0 | | 2666 | 9 | | 2701 | 2.2 | | 2708 | 1 | | 2709 | 6.5 | | 2710 | 5.2 | | 2765 | 6.5 | | 2766 | 15 | | 2767 | 8 | | 71142 | 5 | | 2783 | 4.5 | | 50666 | 6.5 | | 101588 | 9 | | 101588 | 3 | | 3452 | 8.0 | | 23422 | 5 | | 345342 | 6.5 | +-----------+-----------+
RESULT_TABLE:
+-----------+-----------+-----------+------------+ | row_id | val_2 | val_1 | category | +-----------+-----------+-----------+------------+ | 1067 | 2.0 | 6.5 | cat1 | | 2666 | 9 | 6.5 | test | | 2701 | 2.2 | 2.2 | NULL | | 2708 | 1 | 1 | NULL | | 2709 | 6.5 | 1 | NULL | | 2710 | 5.2 | 2.1 | cat1 | | 2710 | 5.2 | 7.1 | test | | 2765 | 6.5 | 1 | NULL | | 2766 | 15 | 1 | NULL | | 2767 | 8 | 3 | test | | 71142 | 5 | 5 | cat1 | | 71142 | 5 | 1 | other | | 2783 | 4.5 | 1 | NULL | | 50666 | 6.5 | 6.5 | other | | 101588 | 9 | 1 | NULL | | 101588 | 3 | 1 | NULL | | 3452 | 8.0 | 1 | NULL | | 23422 | 5 | 1 | NULL | | 345342 | 6.5 | 6.5 | cat1 | | 345342 | 6.5 | 2.8 | test | +-----------+-----------+-----------+------------+I tried to use something like this:
SELECT TABLE_A.row_id, TABLE_A.category, TABLE_A.val_1, TABLE_B.val_2 FROM TABLE_A INNER JOIN TABLE_B ON TABLE_B.row_id = TABLE_A.row_id ORDER BY row_id;However, the result included only rows where the
row_idcolumn exists in TABLE_A.Is there way to connect TABLE_A and TABLE_B to produce the result shown in RESULT_TABLE?
解决方案If you want all the results, you need an outer join, not an inner one. (Inner only returns the rows where there is a match; outer returns all rows, with the matching rows "stitched together")
这篇关于如何在MySQL中连接具有不同行数的两个表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
