SQL-Server中。在聚合函数中使用子查询计算公式

I need to calculate value of Quantity column where Name = A using this formula in SQL-Server:

我需要在SQL-Server中使用此公式计算Quantity列的值，其中Name = A：

A(Quantity) = (B*B1 + C*C1 + D*D1) / SUM(B1 + C1 + D1) + E

Sample data:

样本数据：

Static  Static   Dynamic   
 Name     ID     Quantity  -- Here are more columns, but doesn't matter
 A        1         ? -- need to calculate this value
 B        2         4
 C        3         6
 D        4         8
 E        5         5
 A1       6         -
 B1       7         2
 C1       8         3
 D1       9         4

Have you any ideas how to do It? All values in Quantity column are dynamic, Name and Id columns are static.

你有什么想法怎么做吗？ “数量”列中的所有值都是动态的，“名称”和“Id”列是静态的。

I think something like below, but there is PROBLEM that subqueries can't be used in aggregate function:

我认为如下所示，但有问题是子查询不能用于聚合函数：

SELECT Name,
       Id,
       ISNULL(MAX(CASE WHEN f.Period = YEAR(GETDATE())*100 + 01 THEN
                       CASE WHEN Name = 'A' THEN 
                              (SELECT Quantity FROM Cte WHERE Name = 'A' ) *
                              (SELECT Quantity FROM Cte WHERE Name = 'A1') +
                              (SELECT Quantity FROM Cte WHERE Name = 'B' ) *
                              (SELECT Quantity FROM Cte WHERE Name = 'B1') 
                               ................... 
                            ELSE Quantity 
                       END 
                   END), 0) as Quantity
FROM tbl...

I got following error:

我收到了以下错误：

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Have you any ideas how can I use subquery in aggregate function or any other way to calculate It?

您有什么想法我如何在聚合函数或任何其他方式使用子查询来计算它？

1 个解决方案

#1

I would be inclined to use conditional aggregation to get the values of the variables:

我倾向于使用条件聚合来获取变量的值：

select max(case when name = 'B' then quantity end) as B,
       . . .,
       max(case when name = 'D1' then quantity end) as D
from sample s;

You can then incorporate this into an update:

然后，您可以将其合并到更新中：

with variables as (
      select max(case when name = 'B' then quantity end) as B,
             . . .,
             max(case when name = 'D1' then quantity end) as D
      from sample s
     )
update s
    set s.value = (B*B1 + C*C1 + D*D1) / SUM(B1 + C1 + D1) + E
    from sample s cross join
         variables v
    where name = 'A';

Note: there are other ways to pivot the data; conditional aggregation is just one approach (using pivot, using multiple joins).

注意：还有其他方法来转动数据;条件聚合只是一种方法（使用pivot，使用多个连接）。

#1