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和payroll_advrtn,我应该做完全外连接来获取我的需求结果。但是,我确定完全外连接在mysql中是不可能的,而且我知道完全外连接是可能的,通过使用union.But我不知道如何在以下查询中加入。
My payroll_advance table produce the following result.
我的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 |
+-----+-------+------------+---------+
And payroll_advrtn gives the following result.
payroll_advrtn给出了以下结果。
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!!
任何帮助将不胜感激。谢谢!!
2 个解决方案
#1
3
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
为了执行FULL OUTER JOIN,您可以使用RIGHT OUTER JOIN执行LEFT OUTER JOIN和UNION(假设MySql仍然不支持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
Note that you can use subqueries for A
and B
- which should work with your queries. In your case:
请注意,您可以使用A和B的子查询 - 这应该适用于您的查询。在你的情况下:
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 |
+------+-------+------------+----------+------+-------+------------+----------+
#2
0
Store the results of both queries like this:
存储两个查询的结果,如下所示:
create temporary table a as select ... ;
create temporary table b as select ... ;
Then:
然后:
select * from a left join b using (_tid)
union
select * from a right join b using (_tid);
#1
3
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
为了执行FULL OUTER JOIN,您可以使用RIGHT OUTER JOIN执行LEFT OUTER JOIN和UNION(假设MySql仍然不支持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
Note that you can use subqueries for A
and B
- which should work with your queries. In your case:
请注意,您可以使用A和B的子查询 - 这应该适用于您的查询。在你的情况下:
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 |
+------+-------+------------+----------+------+-------+------------+----------+
#2
0
Store the results of both queries like this:
存储两个查询的结果,如下所示:
create temporary table a as select ... ;
create temporary table b as select ... ;
Then:
然后:
select * from a left join b using (_tid)
union
select * from a right join b using (_tid);