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
對于每個員工，我需要獲得（Task-1 和 Task-2）和 Task-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
因此，如果我Taskname在其中添加Group by它，它會為每個提供多行。我需要為每個員工排一排。需要幫助請。
Name PrepHours ReviewHours
-------------------------------
AAA 2 0
AAA 4 0
AAA 0 3
BBB 0 8
CCC 12 0
CCC 3 0
uj5u.com熱心網友回復：
您可以通過將 Sum 移到 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 Name
uj5u.com熱心網友回復：
您可以在交叉應用中使用條件 case espression創建總計
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/379305.html
標籤：sql sql-server 查询语句
上一篇：如何從日志中獲取特定名稱