sql - T-SQL查询建议

Question

CREATE TABLE #Emp
(
    ID int,
    Name varchar(100)
)

INSERT INTO #Emp 
VALUES (1, 'AAA'), (2, 'BBB'), (3, 'CCC')

CREATE TABLE #Task
(
    EmpID int,
    TaskName varchar(100),
    [Hours] int
)

INSERT INTO #Task 
VALUES (1, 'Task-1', 2), (1, 'Task-2', 4), (1, 'Task-5', 3),
       (2, 'Task-3', 2), (2, 'Task-4', 4), (2, 'Task-5', 3),
       (3, 'Task-1', 2), (3, 'Task-1', 4), (3, 'Task-1', 6),
       (3, 'Task-2', 3), (3, 'Task-6', 1)

#Emp

ID  Name
--------
1   AAA
2   BBB
3   CCC

#任务:

EmpID   TaskName    Hours
-------------------------
1       Task-1       2
1       Task-2       4
1       Task-5       3
2       Task-3       2
2       Task-4       4
2       Task-5       8
3       Task-1       2
3       Task-1       4
3       Task-1       6
3       Task-2       3
3       Task-6       1

对于每个员工，我需要获取(任务 1 和任务 2)和任务 5 的工时总和

类似下面的内容

Name   PrepHours(Task-1 + Task-2)   ReviewHours(Task-5)
-------------------------------------------------------
AAA     6                             3
BBB     0                             8
CCC     15                            0

我尝试了此处显示的查询，但失败并显示错误列#Task.TaskName在选择列表中无效，因为它不包含在聚合函数或GROUP BY中 子句。

SELECT 
    Name, PrepHours, ReviewHours 
FROM 
    #Emp AS E
JOIN
    (SELECT
         empid, 
         CASE 
             WHEN Taskname IN ('Task-1','Task-2') 
                 THEN SUM(Hours) 
                 ELSE 0 
         END AS 'PrepHours',
         CASE 
             WHEN Taskname IN ('Task-5') 
                 THEN SUM(Hours) 
                 ELSE 0 
         END AS 'ReviewHours'
     FROM 
         #Task
     WHERE 
         Taskname IN ('Task-1', 'Task-2', 'Task-5')
     GROUP BY 
         empid) AS t ON E.id = t.empid
ORDER BY
    Name

因此，如果我在 Group by 中添加 Taskname，它会为每个任务提供多行。我需要为每个员工占一行。请需要帮助。

Name    PrepHours   ReviewHours
-------------------------------
AAA      2           0
AAA      4           0
AAA      0           3
BBB      0           8
CCC     12           0
CCC      3           0

Answer 1

您可以在交叉应用中使用条件用例表达式创建总计

select e.name, t.*
from #emp e
cross apply (
    select 
        Sum(case when taskname in ('task-1','task-2') then hours else 0 end) PrepHours,
        Sum(case when taskname ='Task-5' then hours else 0 end) ReviewHours
    from #task t
    where t.EmpId=e.Id
)t