社区首页 >问答首页 >返回多个SUM和多个WHERE的MySQL select语句

问返回多个SUM和多个WHERE的MySQL select语句
EN

Stack Overflow用户

提问于 2018-05-08 23:34:39

回答 2查看 47关注 0票数 0

我有两张桌子。第一个包含库存列表(items)，第二个包含销售历史(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，以及去年这个月(m12)、去年这个月+100万(m11)和去年这个月+200万(m10)期间的SUM或qtyShipped。

+-------------------------------------------------------------------+
|    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   |
+-------------------------------------------------------------------+

谢谢!

mysql

回答 2

Stack Overflow用户

发布于 2018-05-08 23:59:16

尝试将您的查询更改为this...assuming之类的内容，您正在查找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;

票数 0

Stack Overflow用户

发布于 2018-05-09 00:04:37

如果不访问一些示例数据，就很难做出响应，但是这个查询应该可以工作：

SELECT i.id, i.itemDesc, i.qtyOnHandW1, 
   SUM(CASE WHEN l.invDate BETWEEN (NOW() - INTERVAL 12 MONTH) AND (NOW() - INTERVAL 11 MONTH) THEN l.qtyShipped ELSE 0 END) AS m12,
   SUM(CASE WHEN l.invDate BETWEEN (NOW() - INTERVAL 11 MONTH) AND (NOW() - INTERVAL 10 MONTH) THEN l.qtyShipped ELSE 0 END) AS m11,
   SUM(CASE WHEN l.invDate BETWEEN (NOW() - INTERVAL 10 MONTH) AND (NOW() - INTERVAL 9 MONTH) THEN l.qtyShipped ELSE 0 END) AS m10
FROM items i
LEFT JOIN invoiceLines l
ON l.itemCode = i.id
GROUP BY i.id