根据另一列的数据对某一列中的数据进行SUM()操作

Question

我有一个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

现在我需要这个表格看起来像这样：

Project ID       Employee ID          Total Days

1                   100                   10
1                   200                   10

作为一个新手，我对于根据上述条件使用SUM()有些困惑。

- Chow.Net

---

员工ID=200的项目ID = 2？ - Fathah Rehman P

Answer 1

这里有两种方法

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)

方法1:

Select ProjectId,EmployeeId,TotalDays = Sum(TotalDays)
From @t 
Group By ProjectId,EmployeeId

方法2：

;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

结果

ProjectId   EmployeeId  TotalDays
1            100        10
1            200        10

- Niladri Biswas

Answer 2

以下查询产生两列：员工编号（EmployeeID）和总天数（totalDays）。

SELECT  EmployeeID, SUM(totalDays) totalDays
FROM    tableName
GROUP BY EmployeeID

跟进问题：为什么在你期望的结果中，projectId 是 1 和 2？

- John Woo

---

抱歉，所有员工的项目ID都是相同的。我只是按照序列号进行了排序，所有项目的ID都是1。 - Chow.Net

不，所有员工的项目ID都是1。我打错了，抱歉。 - Chow.Net

所以如果您想要包括项目ID，可以将SELECT ProjectID, EmployeeID, SUM(totalDays) totalDays FROM tableName GROUP BY ProjectID, EmployeeID 进一步修改为此。 - John Woo

Answer 3

select min("Project ID")as 'Project ID',"Employee ID"
   , SUM("Total Days") as 'Total Days'
from table1
group by "Employee ID"

- Fathah Rehman P