在ms sql中优化有条件和无条件的聚合

Question

我想从一个列中得到一个总和，有条件和没有条件。我现在拥有的代码是

SELECT regular.id, regular.sum as regularsum, special.sum as specialsum FROM 
(SELECT Sum(stuff) as sum, id FROM table
 WHERE commonCondition = true
 GROUP BY id) as regular
INNER JOIN 
(SELECT Sum(stuff) as sum, id FROM table
 Where commonCondition = true AND specialCondition = true
 GROUP BY id) as special
ON regular.id = special.id

这是有效的，但似乎非常低效，更不用说丑陋了。该表相当大，因此欢迎进行一些优化。

我怎样才能以一种更好、更有效的方式写这篇文章呢？

Answer 1

我认为你可以这样做：

 SELECT 
    Sum(stuff) as regularsum, 
    sum(case when specialcondition=true then stuff else 0 end) as specialsum,
    id FROM table
 WHERE commonCondition = true
 GROUP BY id

但是，您可能希望测试一下它是否更快。