sql聚合函数优化

Question

我正在试图找到一种优化查询的方法，我相信这会占用我的sql查询的巨大time.This：

select
ISNULL((select sum(s.durationp) from Schedule s where AccountID = @AccountID and ClientID = @clientidvalue and status=2 and (@StaffID = 0 OR (@StaffID <> 0 AND s.StaffID = @StaffID)) and s.Date= @sunday), 0),
ISNULL((select sum(s.durationp) from Schedule s where AccountID = @AccountID and ClientID = @clientidvalue and status=2 and (@StaffID = 0 OR (@StaffID <> 0 AND s.StaffID = @StaffID)) and s.Date= @monday), 0),
ISNULL((select sum(s.durationp) from Schedule s where AccountID = @AccountID and ClientID = @clientidvalue and status=2 and  (@StaffID = 0 OR (@StaffID <> 0 AND s.StaffID = @StaffID)) and s.Date= @tuesday), 0),
ISNULL((select sum(s.durationp) from Schedule s where AccountID = @AccountID and ClientID = @clientidvalue and status=2 and (@StaffID = 0 OR (@StaffID <> 0 AND s.StaffID = @StaffID)) and s.Date= @wednesday), 0),
ISNULL((select sum(s.durationp) from Schedule s where AccountID = @AccountID and ClientID = @clientidvalue and status=2 and (@StaffID = 0 OR (@StaffID <> 0 AND s.StaffID = @StaffID)) and s.Date= @thursday), 0),
ISNULL((select sum(s.durationp) from Schedule s where AccountID = @AccountID and ClientID = @clientidvalue and status=2 and (@StaffID = 0 OR (@StaffID <> 0 AND s.StaffID = @StaffID)) and s.Date= @friday), 0),
ISNULL((select sum(s.durationp) from Schedule s where AccountID = @AccountsqlID and ClientID = @clientidvalue and status=2 and (@StaffID = 0 OR (@StaffID <> 0 AND s.StaffID = @StaffID)) and s.Date= @saturday), 0).

我认为查询的每一列都在执行相同的逻辑，除了在日期上的更改作为最后一个约束。有没有办法我可以先计算整个逻辑，比如将结果刷新到临时表中，然后用date作为where子句进行查询。

Answer 1

使用条件聚合：

select sum(case when s.Date = @sunday then s.durationp else 0 end) as sunday,
       sum(case when s.Date = @monday then s.durationp else 0 end) as monday,
       . . .
from Schedule s 
where AccountID = @AccountID and ClientID = @clientidvalue and
      status = 2 and
      (@StaffID = 0 OR (@StaffID <> 0 AND s.StaffID = @StaffID));

注意:假设至少有一行符合WHERE条件，我省略了NULL转换。