在一行上使用某些公式来更新多行. [英] Update Multiple rows using some formula on a row.

问题描述

我有一个表，需要使用一些公式来更新该表的列.示例表和数据如下:-

Ecode	CountyGroupName	Project	Duration
101	NJ	Project1	15
101	CJ	Project1	12
101	PK	Project3	10
102	NJ	Project1	20
102	PK	Project2	10

我想对所有持续时间列进行更新.更新将如下所示:-
ECode 101的总持续时间为15 + 12 + 10 = 37，Ecode 102的总持续时间为20 + 10 = 30.更新将其输出为:-

Ecode	CountyGroupName	Project	Duration
101	NJ	Project1	(15 * 12)/37
101	CJ	Project1	(12 * 12)/37
101	PK	Project3	(10 * 12)/37
102	NJ	Project1	(20 * 12)/30
102	PK	Project2	(10 * 12)/30

在所有持续时间中，都会进行一些计算，公式将为:-
(实际持续时间* 12)/(该ECode的持续时间总和)

请向我提供sql中的查询.

I have a table and need to update the column of that table using some formula. The sample table and data are as below:-

Ecode	CountyGroupName	Project	Duration
101	NJ	Project1	15
101	CJ	Project1	12
101	PK	Project3	10
102	NJ	Project1	20
102	PK	Project2	10

I want to do an update on all duration column.The update will be like this:-
Total duration for ECode 101 is 15+12+10=37 and for Ecode 102 is 20+10=30.The Update will give this as the output:-

Ecode	CountyGroupName	Project	Duration
101	NJ	Project1	(15*12)/37
101	CJ	Project1	(12*12)/37
101	PK	Project3	(10*12)/37
102	NJ	Project1	(20*12)/30
102	PK	Project2	(10*12)/30

As in all the duration some calculation is going and the formula will be :-
(Actual Duration*12)/(Sum Of duration for that ECode)

Please provide me the Query in sql.

推荐答案

UPDATE t1
   SET
      t1.Duration = (t1.Duration*12)/(select SUM(duration) from EmpTable where ECode=t1.ECode)
   FROM
      EmpTable t1,
      EmpTable t2

这篇关于在一行上使用某些公式来更新多行.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！