结果列之和的Reduce SQL语句

Question

select *, 
  (a / 100) as a_calc, 
  (select sum(a_calc) from foo where c = ? and d = ?) as a_calc_sum 
from foo 
where c = ? and d = ? 
order by a;

这里的目的是返回与原始查询匹配的所有行(不包括总和)，并让mysql为我计算a_calc的总和，而不是发出单独的查询。如果没有subselect，我似乎只得到了查询中的第一行附加了正确的总和。

有什么想法可以减少上面的查询吗？

Answer 1

我认为您希望避免为每一行运行子查询--方法如下：

select *, 
  a / 100 as a_calc,
  a_calc_sum
from foo 
join (select sum(a / 100) a_calc_sum from foo where c = ? and d = ?) x
where c = ? and d = ? 
order by a;