mysql - MySQL 合并来自联合查询的记录

Question

任何人都可以帮忙。我有 2 张桌子，发给员工的物品和员工退回的物品。我已经编写了以下联合查询，正如预期的那样，每个结果我得到 2 行

select count(itemcode) as issued, 0 as returned, issue.employee, issue.itemcode 
from issue 
group by itemcode, employee
Union
select 0 as issued, count(itemcode) as returned, return.employee, return.itemcode 
from return 
group by itemcode, employee

结果是：

issued          returned       employee           itemcode
1               0              12345              abcd
0               1              12345              abcd
2               0              00001              abcd
0               3              00002              abcd

我可以做些什么来合并这两条记录，记住并不总是有对应的发出和退回记录。我要求的结果是：

issued          returned       employee           itemcode
1               1              12345              abcd
2               0              00001              abcd
0               3              00002              abcd

任何帮助，将不胜感激。

谢谢史蒂夫

标签： mysqlunion

Answer 1

SELECT SUM(issued) issued,
       SUM(returned) returned,
       employee,
       itemcode
FROM ( select count(itemcode) as issued, 0 as returned, issue.employee, issue.itemcode 
       from issue 
       group by itemcode, employee
     Union
       select 0 as issued, count(itemcode) as returned, return.employee, return.itemcode 
       from return 
       group by itemcode, employee
     ) xxx
GROUP BY itemcode, employee

?