T-SQL查询建议
T-SQL查询建议
提问于 2021-12-10 15:33:46
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在select列表中无效，因为它既不包含在聚合函数中，也不包含在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因此，如果我将Taskname添加到Group by中，它将为每个行提供多个行。我需要每个员工排一排。需要帮助。
Name PrepHours ReviewHours
-------------------------------
AAA 2 0
AAA 4 0
AAA 0 3
BBB 0 8
CCC 12 0
CCC 3 0回答 2
Stack Overflow用户
回答已采纳
发布于 2021-12-10 15:58:08
通过将求和移出case语句，可以使原始查询工作：
SELECT [Name],
PrepHours,
ReviewHours
FROM #Emp AS E
JOIN (SELECT empid,
Sum( CASE
WHEN Taskname IN ( 'Task-1', 'Task-2' ) THEN [Hours]
ELSE 0
END) AS 'PrepHours',
sum(CASE
WHEN Taskname IN ( 'Task-5' ) THEN [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 NameStack Overflow用户
发布于 2021-12-10 15:41:58
您可以在交叉应用中使用条件大小写表达式创建总计。
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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/70306908
复制相关文章
相似问题
腾讯云开发者
