在 T-SQL 中聚合 GREATEST [英] Aggregate GREATEST in T-SQL

问题描述

我的 SQL 生疏了——我有一个简单的要求来计算两列值中较大值的总和:

My SQL is rusty -- I have a simple requirement to calculate the sum of the greater of two column values:

CREATE TABLE [dbo].[Test]
(
    column1 int NOT NULL, 
    column2 int NOT NULL
);

insert into Test (column1, column2) values (2,3)
insert into Test (column1, column2) values (6,3)
insert into Test (column1, column2) values (4,6)
insert into Test (column1, column2) values (9,1)
insert into Test (column1, column2) values (5,8)

在 SQL Server 中没有 GREATEST 函数的情况下，我可以获得两列中较大的一个:

In the absence of the GREATEST function in SQL Server, I can get the larger of the two columns with this:

select column1, column2, (select max(c) 
                            from (select column1 as c
                                   union all
                                  select column2) as cs) Greatest
  from test

我希望我可以简单地总结它们:

And I was hoping that I could simply sum them thus:

select sum((select max(c) 
              from (select column1 as c
                     union all
                    select column2) as cs))
  from test  

但没有骰子:

Msg 130, Level 15, State 1, Line 7
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

这在 T-SQL 中是否可行，而无需求助于过程/临时表?

Is this possible in T-SQL without resorting to a procedure/temp table?

更新:Eran，谢谢 - 我使用了这种方法.然而，我的最终表达有点复杂，我想知道这种情况下的性能:

UPDATE: Eran, thanks - I used this approach. My final expression is a little more complicated, however, and I'm wondering about performance in this case:

SUM(CASE WHEN ABS(column1 * column2) > ABS(column3 * column4)
         THEN column5 * ABS(column1 * column2) * column6
         ELSE column5 * ABS(column3 * column4) * column6 END)

推荐答案

试试这个:

 SELECT SUM(CASE WHEN column1 > column2 
                 THEN column1 
                 ELSE column2 END) 
 FROM test

这篇关于在 T-SQL 中聚合 GREATEST的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！