mysql - 对不同条件下的相同值求和

Question

好吧，标题有点含糊，但我不知道如何更好地简化它。

我有一系列昂贵的类似 SELECT SUM 查询，必须按顺序执行。

例子:

SELECT SUM(t2.Field) 
FROM Table1 AS t1
INNER JOIN (
  SELECT Field FROM Table2
  WHERE [list of where]
) AS t2 ON ti.ExtKey = t2.Key
WHERE t1.TheValue = 'Orange'

SELECT SUM(t2.Field) 
FROM Table1 AS t1
INNER JOIN (
  SELECT Field FROM Table2
  WHERE [list of where]
) AS t2 ON ti.ExtKey = t2.Key
WHERE t1.TheValue = 'Apple'

等等。我使用了嵌套内部联接，因为经过一些测试，它比普通联接更快。

为 Table2 选择的行始终相同，或者至少与 session 相同。有没有一种方法可以将所有查询组合在一起以加快执行速度？

我正在考虑使用 Material View ，但这会使设计和维护变得非常复杂。

Answer 1

我不确定你的目标。我给你一个猜测:

http://sqlfiddle.com/#!9/af66e/2

http://sqlfiddle.com/#!9/af66e/1

SELECT 
  SUM(IF(t1.TheValue = 'Orange',t2.Field,0)) as oranges,
  SUM(IF(t1.TheValue = 'Apple',t2.Field,0)) as apples
FROM Table1 AS t1
INNER JOIN (
  SELECT Field, `key` FROM Table2

) AS t2 ON t1.ExtKey = t2.`key`
# GROUP BY t1.extkey uncomment if you need it

如果您能提供原始数据样本和预期结果，将会有很大帮助。