SQL 基于另一列数据的列中的 SUM() 数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13060782/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SUM() data in a column based on another column data
提问by Chow.Net
I have a sql table
我有一个 sql 表
Project ID Employee ID Total Days
1 100 1
1 100 1
1 100 2
1 100 6
1 200 8
1 200 2
Now i need this table to look like
现在我需要这张桌子看起来像
Project ID Employee ID Total Days
1 100 10
1 200 10
As iam new to sql,i am little confuse to use SUM() based on above condition.
作为 sql 的新手，我对基于上述条件使用 SUM() 有点困惑。
采纳答案by Niladri Biswas
Here are two approaches
这里有两种方法
Declare @t Table(ProjectId Int, EmployeeId Int,TotalDays Int)
Insert Into @t Values(1,100,1),(1,100,1),(1,100,2),(1,100,6),(1,200,8),(1,200,2)
Approach1:
方法一：
Select ProjectId,EmployeeId,TotalDays = Sum(TotalDays)
From @t
Group By ProjectId,EmployeeId
Approach2:
方法二：
;With Cte As(
Select
ProjectId
,EmployeeId
,TotalDays = Sum(TotalDays) Over(Partition By EmployeeId)
,Rn = Row_Number() Over(Partition By EmployeeId Order By EmployeeId)
From @t )
Select ProjectId,EmployeeId,TotalDays
From Cte Where Rn = 1
Result
结果
ProjectId EmployeeId TotalDays
1 100 10
1 200 10
回答by John Woo
This query below produces two columns: EmployeeID, totalDays.
下面的这个查询产生两列：EmployeeID, totalDays。
SELECT EmployeeID, SUM(totalDays) totalDays
FROM tableName
GROUP BY EmployeeID
follow-up question: why is in your desired result the projectIdis 1 and 2?
后续问题：为什么在您想要的结果中projectId是1 and 2？
回答by Fathah Rehman P
select min("Project ID")as 'Project ID',"Employee ID"
, SUM("Total Days") as 'Total Days'
from table1
group by "Employee ID"
