将多个联接合并为单个选择/案例

Question

我目前使用不同的条件查询表4次，然后使用左连接作为更大查询的一部分来返回所有数据。 较大的查询不会运行得特别快，我相当确定我当前的方法效率不高。

我想知道的是是否可以以某种方式使用 CASE 语句来增加 4 列中的一列。 我目前的 4 个查询是：

SELECT ts.department, 
Sum([hours]) AS ChargeableTimeYTD 
FROM   bwbfiles.sos.timesummary ts 
WHERE  category = 'C' 
AND [year] = '2019' 
GROUP  BY department

SELECT ts.department, 
Sum([hours]) AS ChargeableTimeMTD 
FROM   bwbfiles.sos.timesummary ts 
WHERE  category = 'C' 
AND [year] = '2019' 
AND [period] = 4 
GROUP  BY department

SELECT ts.department, 
Sum([hours]) AS NonChargeableTimeProBono 
FROM   bwbfiles.sos.timesummary ts 
WHERE  category = 'NC' 
AND ( [act_code] = '001N' 
OR [act_code] = '00N6' ) 
AND [year] = '2019' 
GROUP  BY department

SELECT ts.department, 
Sum([hours]) AS NonChargeableTimeNonProBono 
FROM   bwbfiles.sos.timesummary ts 
WHERE  category = 'NC' 
AND ( [act_code] <> '001N' 
AND [act_code] <> '00N6' ) 
AND [year] = '2019' 
GROUP  BY department

目的是最终得到一个包含 5 列的查询结果 部门， 收费时间年初至今， 收费时间MTD， 非收费时间无偿， 非收费时间无偿

或者，我将从每个位中删除按部门分组并生成 3 列的查询而不是 CASE。

部门、工时、类别(其中类别为收费时间年初至今/收费时间MTD等...等等...然后将其透视为 5 列。

提前感谢！

Answer 1

这可能会满足我认为您的要求

SELECT ts.department, 
Sum(case when category = 'C' then [hours] else 0 end) AS ChargeableTimeYTD,
Sum(case when category = 'C' and [period] = 4 then [hours] else 0 end) AS ChargeableTimeMTD,
Sum(case when category = 'NC' and ([act_code] = '001N' or [act_code] = '00N6') then [hours] else 0 end) AS NonChargeableTimeProBono,
Sum(case when category = 'NC' and ([act_code] <> '001N' or [act_code] <> '00N6') then [hours] else 0 end) AS NonChargeableTimeNonProBono
FROM   bwbfiles.sos.timesummary ts 
where [year] = '2019'
and [category] in ('C','NC')
GROUP  BY department