MySQL select 语句返回多个 SUM 和多个 WHERE

Question

我有两张 table 。第一个包含库存列表(商品)，第二个包含销售历史记录(invoiceLines)。

+--------------------+  +--------------------+
|        items       +  +    invoiceLines    +
+--------------------+  +--------------------+
+ id                 +  + itemCode           +
+ itemDesc           +  + qtyShipped         +
+ qtyOnHandW1        +  + invDate            +
+--------------------+  +--------------------+

我可以通过单独的查询获取我想要的数据，就像这样:

SELECT itemCode, itemDesc, ROUND(SUM(qtyShipped)) as m11
FROM invoiceLines
WHERE invDate >= (NOW() - INTERVAL 11 MONTH) AND invDate <= (NOW() - INTERVAL 10 MONTH)
AND itemCode = 001
GROUP BY itemCode;

但是，我想从 items 返回 id、itemDesc、qtyOnHandW1，以及去年本月的 SUM 或发货数量 (m12)、去年本月 +100 万 (m11) 和去年本月 +200 万 (m10)。

+-------------------------------------------------------------------+
|    id    |     itemDesc      |   qtyOH  |  m12   |  m11  |  m10   |
+-------------------------------------------------------------------+
+ 001      | product A         |    50    |   32   |   25  |   70   |
+-------------------------------------------------------------------+
+ 002      | product B         |    31    |   16   |   31  |   41   |
+-------------------------------------------------------------------+
+ 003      | product C         |    5     |   22   |   11  |    3   |
+-------------------------------------------------------------------+

谢谢!

Answer 1

尝试将您的查询更改为类似这样的内容...假设您正在查找 2017 年引用的月份...

SELECT 
 a.itemCode as itemCode, 
 a.itemDesc as itemCode, 
 (select ROUND(SUM(qtyShipped)) 
  from invoiceLines 
  where date_format(invDate,'%Y%m') = '201712' and itemCode = a.itemCode 
  group by date_format(invDate,'%Y%m'),itemCode ) as m12,
 (select ROUND(SUM(qtyShipped)) 
  from invoiceLines 
  where date_format(invDate,'%Y%m') = '201711' and itemCode = a.itemCode
  group by date_format(invDate,'%Y%m'),itemCode ) as m11,
 (select ROUND(SUM(qtyShipped)) 
  from invoiceLines 
  where date_format(invDate,'%Y%m') = '201710' and itemCode = a.itemCode
  group by date_format(invDate,'%Y%m'),itemCode ) as m10 
FROM invoiceLines as a
AND a.itemCode = 001
GROUP BY a.itemCode;