SQL-如何通过涉及多列的计算来获得每行的最大值

Question

Table1的每一行都应该在计算中找到包括Table1和Table2中多列的最大值。表1由列组成：ID、Code、Value1、Value2、Max_value表2由以下列组成：ID、Code、Perc1、Perc2

**Table1**
ID=1 (value1=1000, Value2=5000, Code=21);   Max_Value=? 
ID=2 (value1=2000, Value2=4000, Code=21);   Max_Value=?
ID=3 (value1=1000, Value2=5000, Code=21);   Max_Value=?
**Table2**
ID=25 (Perc1=5.2%, Perc2=3.5%, Code=21);    
ID=26 (Perc1=3.1%, Perc2=8.6%, Code=21);   
ID=27 (Perc1=2.5%, Perc2=3.4%, Code=21)

** Calculation**
formula:
𝑀𝑎𝑥𝑉𝑎𝑙𝑢𝑒=𝑀𝐴𝑋[(𝑉𝑎𝑙𝑢𝑒1 ∗ 𝑃𝑒𝑟𝑐1)+(𝑉𝑎𝑙𝑢𝑒2 ∗ 𝑃𝑒𝑟𝑐2)]
WHERE Table1.Code = Table2.Code
Max_Value (Table1.Id=1, Table2.Id=25) = 227
Max_Value (Table1.Id=1, Table2.Id=26) = 461 (max for ID 1)
Max_Value (Table1.Id=1, Table2.Id=27) = 195
Max_Value (Table1.Id=2, Table2.Id=25) = 224
Max_Value (Table1.Id=2, Table2.Id=26) = 406 (max for ID 2)
Max_Value (Table1.Id=2, Table2.Id=27) = 186
Max_Value (Table1.Id=3, Table2.Id=25) = 401
Max_Value (Table1.Id=3, Table2.Id=26) = 695 (max for ID 3)
Max_Value (Table1.Id=3, Table2.Id=27) = 313

Answer 1

如果我理解正确，您需要join和row_number():

select t12.*
from (select t1.*, t2.*,
(t2.perc1 * t1.value1 + t2.perc2 * t1.value2) as calc,
row_number() over (partition by t1.id order by (t2.perc1 * t1.value1 + t2.perc2 * t1.value2) desc) as seqnum
from table1 t1 join
table2 t2
on t1.code = t2.code
) t12
where seqnum = 1;

您的公式和解释暗示了table1中的code列，尽管我在示例数据中没有看到它。