php - 左连接的 MySQL 查询问题

Question

我想从 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 FIFFLE

Answer 1

试试这个:

SELECT date_format(items.date, '%M %Y') as month, 
COALESCE(SUM(items.amount), 0) as Rreceived, 

  (SELECT SUM(amount)
  FROM issued_items
  WHERE date_format(items.date, '%Y-%m') = date_format(date, '%Y-%m') 
    AND items.currency = currency 
  ) AS Issued

FROM items
GROUP BY date_format(items.date, '%Y-%m')