如何做完整的外部联接，以结合MySQL中的两个表? [英] How to do full outer join to combine two tables in mysql?

问题描述

我有两个表 payroll_advance 和 payroll_advrtn ，我应该进行完全外部联接以获取所需的结果.但是，我确定完全外部联接是'在mysql中是不可能的，我也知道可以通过使用union进行完全外部联接.但是我不知道如何在以下查询中进行联接.

I have two tables payroll_advance and payroll_advrtn,and i supposed to do full outer join to get my require result.But,I'm sure full outer join isn't possible in mysql and also i know that full outer join is possible by using the union.But i don't know how can i do join at the following query.

我的 payroll_advance 表产生以下结果.

SELECT _id,_tid,_dt,sum(_amount) as _advance FROM payroll_advance WHERE YEAR( _dt )=YEAR(CURDATE()) AND MONTH(_dt) = MONTH(CURDATE()) group by _tid;

+-----+-------+------------+---------+
| _id | _tid  | _dt        | _advance|
+-----+-------+------------+---------+
|  17 | hjg   | 2012-04-18 |    2151 |
|  22 | RKT01 | 2012-04-10 |    2098 |
|  14 | RKT04 | 2012-04-18 |    1511 |
|  16 | RKT09 | 2012-04-09 |     250 |
|  15 | RKT10 | 2012-04-17 |    1313 |
|   8 | RKT21 | 2012-04-03 |    1321 |
|  19 | RKT31 | 2012-04-26 |    2512 |
|  20 | RKT33 | 2012-04-10 |    2250 |
|  25 | T01   | 2012-04-11 |    2500 |
+-----+-------+------------+---------+

payroll_advrtn给出以下结果.

And payroll_advrtn gives the following result.

SELECT _id,_tid,_dt,sum(_amount) as _advrtn FROM payroll_advrtn WHERE YEAR( _dt ) = YEAR(CURDATE()) AND MONTH(_dt) = MONTH(CURDATE()) group by _tid;
+-----+-------+------------+---------+
| _id | _tid  | _dt        | _advrtn |
+-----+-------+------------+---------+
|   9 | RKT02 | 2012-04-10 |    2500 |
|   8 | RKT04 | 2012-04-20 |     150 |
+-----+-------+------------+---------+

但是我想要通过结合以上两个结果来获得类似以下结果的结果.

But i want something like the following result by combining the above two result.

 +------+-------+-------+------------+----------+---------+
| _id  | _tid  | _tid  | _dt        | _advance | _advrtn |
+------+-------+-------+------------+----------+---------+
|   17 | hjg   | NULL  | 2012-04-18 |     2151 |    NULL |
|   22 | RKT01 | NULL  | 2012-04-10 |      999 |    NULL |
|   14 | RKT04 | RKT04 | 2012-04-18 |       11 |     150 |
|   16 | RKT09 | NULL  | 2012-04-09 |      250 |    NULL |
|   15 | RKT10 | NULL  | 2012-04-17 |     1313 |    NULL |
|    8 | RKT21 | NULL  | 2012-04-03 |     1321 |    NULL |
|   19 | RKT31 | NULL  | 2012-04-26 |     2512 |    NULL |
|   20 | RKT33 | NULL  | 2012-04-10 |     2250 |    NULL |
|   25 | T01   | NULL  | 2012-04-11 |     2500 |    NULL |
| NULL | NULL  | RKT02 | NULL       |     NULL |    2500 |
+------+-------+-------+------------+----------+---------+

任何帮助将不胜感激.谢谢！

Any help will be appreciated.Thanks!!

推荐答案

为了执行FULL OUTER JOIN

In order to do the FULL OUTER JOIN you can do the LEFT OUTER JOIN and UNION with RIGHT OUTER JOIN (provided that MySql still does not support FULL OUTER JOIN):

select * from A as a
    left outer join B as b on a.col = b.col
union
select * from A as a
    right outer join B as b on a.col = b.col

请注意，您可以对A和B使用子查询-应该与您的查询一起使用.就您而言:

Note that you can use subqueries for A and B - which should work with your queries. In your case:

select * from (SELECT * FROM t1) as a
    left outer join (SELECT * FROM t2) as b on a._tid = b._tid
union
select * from (SELECT * FROM t1) as a
    right outer join (SELECT * FROM t2) as b on a._tid = b._tid

结果等于(只要我在复制粘贴数据时没有错):

With result being equal to (provided that I didn't my a mistake in copy-pasting your data):

+------+-------+------------+----------+------+-------+------------+----------+
| _id  | _tid  | _dt        | _advance | _id  | _tid  | _dt        | _advartn |
+------+-------+------------+----------+------+-------+------------+----------+
|   17 | hjg   | 2012-04-18 |     2151 | NULL | NULL  | NULL       |     NULL |
|   22 | RKT01 | 2012-04-10 |     2098 | NULL | NULL  | NULL       |     NULL |
|   14 | RKT04 | 2012-04-18 |     1511 |    8 | RKT04 | 2012-04-20 |      150 |
|   16 | RKT09 | 2012-04-09 |      250 | NULL | NULL  | NULL       |     NULL |
|   15 | RKT10 | 2012-04-17 |     1313 | NULL | NULL  | NULL       |     NULL |
|    8 | RKT21 | 2012-04-03 |     1321 | NULL | NULL  | NULL       |     NULL |
|   19 | RKT31 | 2012-04-26 |     2512 | NULL | NULL  | NULL       |     NULL |
|   20 | RKT33 | 2012-04-10 |     2250 | NULL | NULL  | NULL       |     NULL |
|   25 | T01   | 2012-04-11 |     2500 | NULL | NULL  | NULL       |     NULL |
| NULL | NULL  | NULL       |     NULL |    9 | RKT02 | 2012-04-10 |     2500 |
+------+-------+------------+----------+------+-------+------------+----------+

这篇关于如何做完整的外部联接，以结合MySQL中的两个表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！