在 ms sql 中优化带条件和不带条件的聚合

发布于 2024-09-25 19:52:29 字数 484 浏览 3 评论 0原文

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

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

Which Works，但看起来效率很低，更不用说丑陋了。该表相当大，因此欢迎进行一些优化。

我怎样才能以更好、更有效的方式写这个？

原文

I would like to get a sum from a column, with and without a condition. The code I have now is

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

Which works, but seems very inefficient, not to mention ugly. the table is fairly large, so some optimisation would be welcome.

How can I write this in a better, more efficent way?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

っ〆星空下的拥抱 2024-10-02 19:52:29

我认为你可以这样做：

 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

但是，你想测试一下它是否更快。

I think you could do something like this:

 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

However, you'd want to test to see if it was any faster.

回复收藏 0 原文

~没有更多了~