MySQL UNION ALL和LEFT JOIN返回双值

Question

我有多个表试图在MySql中使用UNION all和LEFT JOIN从所有表中获取值，我使用的查询如下所示。

(SELECT 
t1.name, t2.cat, t3.utt, t4.gcost, t4.net
FROM table_one as t1
LEFT JOIN table_three as t2
ON t2.id = t1.ct
LEFT JOIN table_four as t3
ON t3.id = t1.ut
LEFT JOIN table_five as t4
ON t4.gid = t1.id
) UNION ALL
(SELECT 
t1.name, t2.cat, t3.utt, t4.gcost, t4.net
FROM table_tow as t1
LEFT JOIN table_three as t2
ON t2.id = t1.ct
LEFT JOIN table_four as t3
ON t3.id = t1.ut
LEFT JOIN table_five as t4
ON t4.gid = t1.id
)

但是上面的查询在MySQL 中返回两个值

以下是fiddle链接：http://sqlfiddle.com/#！9/bd30ba/1

任何帮助都将不胜感激。

感谢

更新

根据p.Salmon的问题，了解上述查询用于固定资产应用程序的完整逻辑，因此固定资产有多个项目值，如：

1. 实际价格
2. 累计折旧
3. 当前值

所以查询中有两个不同的价格/金额，查询应该为每个引用行id返回两个值。

Answer 1

感谢大家的帮助，但这些都没有帮助我，我知道table_five中有重复的ID，从table_one到table_tow。。。我解决问题的唯一方法是在table_one、table_tow中添加新的序列ID。。。作为

seq_.time();

插入期间在table_five中添加相同的序列。。之后，我将查询更改为.

(SELECT 
t1.name, t2.cat, t3.utt, t4.gcost, t4.net
FROM table_one as t1
LEFT JOIN table_three as t2
ON t2.id = t1.ct
LEFT JOIN table_four as t3
ON t3.id = t1.ut
LEFT JOIN table_five as t4
ON t4.gid = t1.id
AND t4.seq = t1.seq
) UNION ALL
(SELECT 
t1.name, t2.cat, t3.utt, t4.gcost, t4.net
FROM table_tow as t1
LEFT JOIN table_three as t2
ON t2.id = t1.ct
LEFT JOIN table_four as t3
ON t3.id = t1.ut
LEFT JOIN table_five as t4
ON t4.gid = t1.id
AND t4.seq = t1.seq
)

现在我得到了我想要的准确数据。感谢

Answer 2

你只想要一个，真的不在乎哪一个，然后最大化t5细节

select t1.name,t3.cat,t4.utt, 
t5.gcost,t5.net
from
(
select * from table_one
union all
select * from table_two) t1
LEFT JOIN table_three as t3 ON t3.id = t1.ct
LEFT JOIN table_four as t4            ON t4.id = t1.ct
left join (select gid,max(gcost) gcost,max(net) net from table_five group by gid) t5 on t5.gid = t1.id;
+--------------+------+------+-------+------+
| name         | cat  | utt  | gcost | net  |
+--------------+------+------+-------+------+
| Green Apple  | A    | AAA  |  3000 | 6542 |
| Small Orange | B    | BBB  |  2560 | 6542 |
| Small Banana | C    | CCC  |  5800 | 6542 |
| Small Mango  | C    | DDD  |  2560 | 6542 |
| Red Apple    | A    | AAA  |  3000 | 6542 |
| Big Orange   | B    | BBB  |  2560 | 6542 |
| Big Banana   | C    | CCC  |  5800 | 6542 |
| Big Mango    | C    | DDD  |  2560 | 6542 |
+--------------+------+------+-------+------+
8 rows in set (0.072 sec)