左联接上的MySQL查询问题

debugcn 发表于 Dev

萨利姆

我正在尝试从2个表中获取每月的总金额，这是我的查询

SELECT date_format(items.date, '%M %Y') as month, 
COALESCE(SUM(items.amount), 0) as Rreceived, 
COALESCE(SUM(issued_items.amount), 0) as Issued, 
items.currency 
FROM items 
LEFT JOIN issued_items 
ON date_format(items.date, '%Y-%m')=date_format(issued_items.date, '%Y-%m') 
AND items.currency=issued_items.currency 
GROUP BY date_format(items.date, '%Y-%m')

我收错了金额。您能帮我更正查询吗？

SQL文件

伊凡·布蒂诺尼（Ivan Buttinoni）

添加与新的http://sqlfiddle.com/#!2/9856e9/1情况相关的新答案：

为了避免行丢失，我在两个表上都进行了合并，以选择所有可能的月份/货币组合，因此我可以在两个表上进行左联接。请注意，如果工会中不存在组合（2013年4月/美元），则不会返回任何行！

SELECT union_items.month, 
ifnull(items.amount,0) as Received, 
ifnull(issued_items.amount,0) as Issued, 
union_items.currency 
FROM ( 
      select 
      date_format(items.date, '%M %Y') as month, items.currency 
      FROM items  
      GROUP BY date_format(items.date, '%Y-%m')
      UNION 
      select 
      date_format(issued_items.date, '%M %Y') as month, issued_items.currency 
      FROM issued_items  
      GROUP BY date_format(issued_items.date, '%Y-%m')
) union_items
LEFT JOIN
(
SELECT date_format(items.date, '%M %Y') as month, 
COALESCE(SUM(items.amount), 0) as amount, 
items.currency 
FROM items  
GROUP BY date_format(items.date, '%Y-%m')
) items 
ON items.month=union_items.month 
AND items.currency=union_items.currency 
LEFT JOIN (
SELECT date_format(issued_items.date, '%M %Y') as month, 

  COALESCE(SUM(issued_items.amount), 0) as amount, 
issued_items.currency 
FROM issued_items
  GROUP BY date_format(issued_items.date, '%Y-%m')

)
issued_items 
ON union_items.month=issued_items.month 
AND union_items.currency=issued_items.currency

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。