Aggregate GREATEST in T-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)

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  

But no dice:

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

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

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)

回答1:

Try this:

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

回答2:

Try this... Its not the best performing option, but should work.

SELECT
  'LargerValue' = CASE 
                   WHEN SUM(c1) >= SUM(c2) THEN SUM(c1)
                   ELSE SUM(c2)
                  END
FROM Test

回答3:

SELECT
    SUM(MaximumValue)
FROM (
    SELECT 
        CASE WHEN column1 > column2
        THEN
            column1
        ELSE
            column2
        END AS MaximumValue
    FROM
        Test
) A

回答4:

FYI, the more complicated case should be fine, so long as all of those columns are part of the same table. It's still looking up the same number of rows, so performance should be very similar to the simpler case (as SQL Server performance is usually IO bound).

回答5:

How to find max from single row data

 -- eg (empid , data1,data2,data3  )
    select emplid , max(tmp.a)
    from
      (select emplid,date1 from table
      union 
      select emplid,date2 from table 
      union 
      select emplid,date3 from table
    ) tmp , table
    where tmp.emplid = table.emplid

回答6:

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

回答7:

The best answer to this is simply put :

;With Greatest_CTE As
(
  Select ( Select Max(ValueField) From ( Values (column1), (column2) ) ValueTable(ValueField) ) Greatest
    From Test
)
Select Sum(Greatest)
  From Greatest_CTE

It scales a lot better than the other answers with more than two value columns.