sql求和然后计数？

ymzxtsji 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(294)

表历史库存
列：日期、产品id、库存、库存分配
我想计算一个产品（whstock-stock allocated）在日期x和y之间<=0的次数，其中prod\u id=“id1”
有什么想法吗？
我是沿着

SELECT Count `Prod_ID`
     , sum((`WHStock`-`Stock_Allocated`)) as Stock 
  from Historic_Inventory 
 WHERE `Date` Between '2018-04-01'and '2018-08-01'
   AND Stock <= 0

但效果不太好。。

sql mysql count sum

来源：https://stackoverflow.com/questions/51576805/sql-sum-then-count

2条答案

按热度按时间

xlpyo6sf1#

我想你想要：

SELECT COUNT(*)
FROM (SELECT Prod_ID, SUM(WHStock - Stock_Allocated) as Stock 
      FROM Historic_Inventory 
      WHERE `Date` Between '2018-04-01' and '2018-08-01'
      GROUP BY Prod_ID
     ) x
WHERE Stock <= 0;

嗯。这将统计值为负值的产品数。
也许你想要：

select hi.*
       (@stock := @stock + (WHStock - Stock_Allocated)) as stock
from (select hi.*
      from historic_inventory hi
      where prod_id = ?
      order by date
     ) hi cross join
     (select @stock := 0) params
having stock < 0;

赞(0）回复(0）举报 2021-06-20

n8ghc7c12#

试试这个：

SELECT Count(*)
FROM
(SELECT SUM((`WHStock`-`Stock_Allocated`)) AS Stock 
FROM Historic_Inventory 
WHERE `Date` Between '2018-04-01'and '2018-08-01') AS t
WHERE t.Stock <= 0

赞(0）回复(0）举报 2021-06-20

我来回答

sql求和然后计数？

2条答案

相关问题

热门标签

最新问答