将一行字段设置为 2 个其他字段的乘积

问题描述

我有一个这样的SQL表结构

Id A B C D1 1 5 6 252 2 10 5 253 3 7 4 254 1 6 5 265 2 10 5 266 3 8 3 26

我想写一个脚本，它将更新所有B &A=3 的行中的 C 列 与 A = 1 和 A = 2 相乘的值(对于相同的 D 列)

所以结果应该是

如何在 SQL 中编写这样的代码?

一种可能的方法是将表与自身连接两次:

更新T3放T3.B = T1.B * T2.B，T3.C = T1.C * T2.C来自 [表] T3在 T1.A = 1 和 T1.D = T3.D 上加入 [表] T1在 T2.A = 2 和 T2.D = T3.D 上加入 [表] T2在哪里T3.A = 3

I have a such a structure of SQL table

Id    A     B    C   D
1     1     5    6   25
2     2     10   5   25
3     3     7    4   25
4     1     6    5   26
5     2     10   5   26
6     3     8    3   26

I want to write a script, which will update all the B & C columns in the rows with A=3 with the value of multiplication of the A = 1 and A = 2 (for the same value of D column)

So the result should be

Id    A     B    C   D
1     1     5    6   25
2     2     10   5   25
3     3     50   30  25
4     1     6    5   26
5     2     10   5   26
6     3     60   25  26

How can I write such a code in SQL?

One possible way is joining table to itself twice:

update T3
set
    T3.B = T1.B * T2.B,
    T3.C = T1.C * T2.C
from [Table] T3
    join [Table] T1 on T1.A = 1 and T1.D = T3.D
    join [Table] T2 on T2.A = 2 and T2.D = T3.D
where
    T3.A = 3