mysql - SQL计算两个不同表的计数之间的比率

Question

我有三张表:带有列 ID、描述的产品
要求列 id、分支
带有列 id、分支的库存

我想计算每个产品和分支的比率，并得到这样的结果

product branch demands stock ratio
shoes   A      4       8     2
shoes   B      8       4     0.5
shirts  A      1       1     1

我以为这很容易，但之后我陷入困境

SELECT products.id, products.description,demands.branch, COUNT(demands.branch)
FROM products
LEFT JOIN demands ON (demands.id=products.id)
WHERE demands.id IS NOT NULL
GROUP BY products.id, demands.branch

可能存在没有库存的需求，而我正在使用嵌入管理系统中的 mySQL 数据库

Answer 1

如果我理解正确，一种方法使用union all:

select id, branch, sum(demand), sum(stock), sum(stock) / nullif(sum(demand), 0) as ratio
from ((select id, branch, 1 as demand, 0 as stock
       from demands
      ) union all
      (select id, branch, 0 as demand, 1 as stock
       from stock
      )
     ) ds
group by id, branch;