find max value in a row and update new column with

问题:

I have a table like this

number  col1   col2   col3   col4  max
---------------------------------------
  0     200    150    300     80         
 16      68    250    null    55        

I want to find max value between col1,col2,col3,col4 in every row and update the last column "max" with the max value column name!

for example in first row max value is 300 the "max" column value will be "col3" result like this:

number   col1   col2   col3    col4   max
------------------------------------------
  0      200    150    300      80    col3
 16       68    250    null     55    col2

How can I do this?

回答1:

QUERY

SELECT *,(
SELECT MAX(n) 
    FROM
    (
        VALUES(col1),(col2),(col3),(col4)
    ) AS t(n)
)  AS maximum_value
FROM #tmp

回答2:

Update statement

with MaxValues
    as (select [number], [max] = (
          select (
            select max ([n])
              from (values ([col1]) , ([col2]) , ([col3]) , ([col4])) as [t] ([n])
          ) as [maximum_value])
          from [#tmpTable]) 
    update [#tmpTable]
      set [max] = [mv].[max]
      from [MaxValues] [mv]
           join [#tmpTable] on [mv].[number] = [#tmpTable].[number];

assuming number is a key column

回答3:

SQL Fiddle

Check in SQL Fiddle

Schema

DECLARE @temp table ([number] int NOT NULL, [col1] int, [col2] int, [col3] int, [col4] int, [colmax] int);

INSERT @temp VALUES (0, 200, 150, 300, 80, null), (16, 68, 250, null, 55, null);

Query

SELECT number
    ,(
        SELECT MAX(col) maxCol
        FROM (
            SELECT t.col1 AS col

            UNION

            SELECT t.col2

            UNION

            SELECT t.col3

            UNION

            SELECT t.col4
            ) a
        ) col
FROM @temp t

and the update statement is -

UPDATE tempCol
SET colmax = a.col
FROM (
SELECT (
        SELECT MAX(col) maxCol
        FROM (
            SELECT t.col1 AS col

            UNION

            SELECT t.col2

            UNION

            SELECT t.col3

            UNION

            SELECT t.col4
            ) a
        ) col
FROM tempCol t
) a