SQL Server 添加条件为验证的所有行

Question

我有一个SQL Server数据库，我需要在其中添加作业系列的所有成本。

我有一张这样的桌子

表：工作

+-------+-----------+-----------+---------+
| wonum |   cost1   |   cost2   | wogroup |
+-------+-----------+-----------+---------+
|   1   |   30.12   |  157.14   |    1    |
|   2   |   110.10  |    0.00   |    1    |
|   3   |   12.67   |   45.45   |    1    |
|   4   |   0.00    |    0.00   |    4    |
|   5   |   400.00  |   11.54   |    4    |
+-------+-----------+-----------+---------+ 

我需要为所有具有相同wogroup的行添加 cost1 和 cost2，但仅适用于 onwhere wonum = wogroup.

喜欢这个

+-------+-----------+-----------+---------+---------+
| wonum |   cost1   |   cost2   | wogroup |  total  |
+-------+-----------+-----------+---------+---------+
|   1   |   30.12   |  157.14   |    1    | 355.48  |
|   2   |   110.10  |    0.00   |    1    |  null   |
|   3   |   12.67   |   45.45   |    1    |  null   |
|   4   |   0.00    |    0.00   |    4    |  411.54 |
|   5   |   400.00  |   11.54   |    4    |  null   |
+-------+-----------+-----------+---------+---------+

在一个完美的世界中，null 值将是该行的成本 1 和成本 2 的总和，但我不确定这是否可能......

编辑：我只能做一个选择，它是为了BiRT报告

Answer 1

由于这可能会随着添加更多 wonum 而改变，因此我会将其作为VIEW

declare @work table (wonum  int , cost1 decimal (6,3),  cost2   decimal (6,3) , wogroup int)
insert into @work
values
(1,30.12,157.14,1),
(2,110.10,0.00,1),
(3,12.67,45.45,1),
(4,0.00,0.00,4),
(5,400.00,11.54,4)

select
*,
total = case when wonum = min(wonum) over (partition by wogroup) then sum(cost1) over (partition by wogroup) + sum(cost2) over (partition by wogroup) end 
from @work

返回

+-------+-----------+-----------+---------+---------+
| wonum |   cost1   |   cost2   | wogroup |  total  |
+-------+-----------+-----------+---------+---------+
|   1   |   30.12   |  157.14   |    1    | 355.48  |
|   2   |   110.10  |    0.00   |    1    |  null   |
|   3   |   12.67   |   45.45   |    1    |  null   |
|   4   |   0.00    |    0.00   |    4    |  411.54 |
|   5   |   400.00  |   11.54   |    4    |  null   |
+-------+-----------+-----------+---------+---------+

您的查询

select
*,
total = case when wonum = min(wonum) over (partition by wogroup) 
then sum(cost1) over (partition by wogroup) + sum(cost2) over (partition by wogroup) 
else null 
end 
from work