MySQL sum()在不同的分组依据上 [英] MySQL sum() on different group bys

问题描述

好吧，我对两个表进行了查询.我需要拿两笔款项.我做了group by，所以sum()可以正常工作.

Ok, I have a query over two tables. I need to get two sums. I do a group by so the sum() works correctly.

SELECT sum(a.x), sum(b.y) FROM a,b GROUP BY a.n where a.n=b.m

到目前为止，这很好，但是问题是我需要对第二个和(sum(b.y))进行分组，而不是对第一个和(sum(a.x))进行分组.

So far this works well, but the problem is i need to group them differently for the second sum (sum(b.y)), than for the first sum (sum(a.x)).

真正的查询要复杂一些，但这是我的主要问题.

The real query is somewhat more complex but this is my main problem.

这是我实际上尝试选择的sum(stock.amount) - if( sold.amount IS NULL , 0, sum( sold.amount ) )

This is what i actually try to select sum(stock.amount) - if( sold.amount IS NULL , 0, sum( sold.amount ) )

如何在一个查询中解决这个问题?

How can I solve that in one query?

推荐答案

因为您没有写下表格，所以我会做出一个疯狂的猜测，并假设表格就像:

since you are not writing down the tables I am gonna make a wild guess and assume the tables are like :

库存:编号，item_id，金额 已售:id，item_id，金额

stock : id, item_id, amount sold : id, item_id, amount

然后我再次假设您需要stock_in_total，sold_total，left_total总数

then again I assume that you need the stock_in_total, sold_total, left_total counts

SELECT 
  stock_sums.item_id, 
  stock_sums.st_sum as stock_in_total,
  COALESCE(sold_sums.so_sum,0) as sold_total,
  (stock_sums.st_sum - COALESCE(sold_sums.so_sum,0)) as left_total
FROM (
  SELECT stock.item_id as item_id, SUM(stock.amount) as st_sum 
  FROM stock 
  GROUP BY item_id
) as stock_sums 
LEFT JOIN (
  SELECT sold.item_id as item_id, SUM(sold.amount) as so_sum 
  FROM sold
  GROUP by item_id
) as sold_sums ON stock_sums.item_id = sold_sums.item_id

我希望这会有所帮助.

这篇关于MySQL sum()在不同的分组依据上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！