MySQL SELECT 运行多次

Question

当我运行我的 SELECT 语句时，它会运行 IFNULL 部分三次和 SUM CASE 部分两次。我知道这与语句中的 LEFT JOIN 过多有关。但无法找到问题的解决方案。

很难解释这个问题，所以我包含了 sqlfiddle 链接。

SQLFIDDLE

SELECT 
IFNULL(SUM(d.quantity),0) as dispatch, 
SUM(CASE WHEN t.valuefor='alpha' THEN t.quantity ELSE 0 END) as alpha,
SUM(CASE WHEN t.valuefor='beta' THEN t.quantity ELSE 0 END) as beta 
FROM conversion as c 
LEFT JOIN sale as s ON s.conversionid = c.id 
LEFT JOIN dispatch as d ON d.saleid = s.id 
LEFT JOIN test as t ON t.conversionid = c.id

电流输出

dispatch    alpha   beta
1800        1400    80

期望的输出

dispatch    alpha   beta
600         700     40

Answer 1

您需要将调度值和测试值的总和分开 - 这样的事情可能会给出您想要的答案:

Select A.dispatch,
SUM(CASE WHEN t.valuefor='alpha' THEN t.quantity ELSE 0 END) as alpha,
SUM(CASE WHEN t.valuefor='beta' THEN t.quantity ELSE 0 END) as beta 
FROM(
  SELECT sum(quantity) AS dispatch, c.id
  FROM conversion as c 
  LEFT JOIN sale as s ON s.conversionid = c.id 
  LEFT JOIN dispatch as d ON d.saleid = s.id
  GROUP BY d.saleid) A
LEFT JOIN test as t ON t.conversionid = A.id
group by A.id;

内部子查询总计分派(dispatch)值，然后加入测试量。