查找哪个COLUMN具有最大值（每行值）

MSSQL

Table looks like so

表看起来像这样

ID    1   | 2  |  3  |  4  |  5 
AA1   1   | 1  |  1  |  2  | 1

any clues on how I could make a query to return

关于如何使查询返回的任何线索

ID  |  MaxNo
AA1 | 4

, usign the above table example? I know I could write a case blah when statement, but I have a feeling there's a much simpler way of doing this

，使用上表示例？我知道我可以在声明时写一个案例，但我觉得有一个更简单的方法

2 个解决方案

#1

You can use UNPIVOT to get these comparable items, correctly¹, into the same column, and then use ROW_NUMBER() to find the highest valued row²:

您可以使用UNPIVOT将这些可比较的项目（正确1）放入同一列，然后使用ROW_NUMBER（）查找值最高的row2：

declare @t table (ID char(3) not null,[1] int not null,[2] int not null,
                 [3] int not null,[4] int not null,[5] int not null)
insert into @t (ID,[1],[2],[3],[4],[5]) values
('AA1',1,1,1,2,1)

;With Unpivoted as (
select *,ROW_NUMBER() OVER (ORDER BY Value desc) rn
from @t t UNPIVOT (Value FOR Col in ([1],[2],[3],[4],[5])) u
)
select * from Unpivoted where rn = 1

Result:

结果：

ID   Value       Col                       rn
---- ----------- ------------------------- --------------------
AA1  2           4                         1

¹ If you have data from the same "domain" appearing in multiple columns in the same table (such that it even makes sense to compare such values), it's usually a sign of attribute splitting, where part of your data has, incorrectly, been used to form part of a column name.

1如果您在同一个表中的多个列中显示来自同一“域”的数据（这样比较这些值甚至是有意义的），它通常是属性拆分的标志，其中部分数据错误地已经存在用于形成列名的一部分。

² In your question, you say "per row", and yet you've only given a one row sample. If we assume that ID values are unique for each row, and you want to find the maximum separately for each ID, you'd write the ROW_NUMBER() as ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Value desc) rn, to get (I hope) the result you're looking for.

2在你的问题中，你说“每行”，但你只给出了一行样本。如果我们假设ID值对于每一行都是唯一的，并且您想要为每个ID单独找到最大值，那么您将ROW_NUMBER（）写为ROW_NUMBER（）OVER（PARTITION BY ID ORDER BY Value desc）rn，以获取（我希望）你正在寻找的结果。

#2

You can use a cross apply where you do max() over the columns for one row.

您可以使用交叉应用，在一行的列上执行max（）。

select T1.ID,
       T2.Value
from YourTable as T1
  cross apply
    (
    select max(T.Value) as Value
    from (values (T1.[1]),
                 (T1.[2]),
                 (T1.[3]),
                 (T1.[4]),
                 (T1.[5])) as T(Value)
    ) as T2

If you are on SQL Server 2005 you can use union all in the derived table instead of values().

如果您使用的是SQL Server 2005，则可以在派生表中使用union all而不是values（）。

select T1.ID,
       T2.Value
from YourTable as T1
  cross apply
    (
    select max(T.Value) as Value
    from (select T1.[1] union all
          select T1.[2] union all
          select T1.[3] union all
          select T1.[4] union all
          select T1.[5]) as T(Value)
    ) as T2

SQL Fiddle

SQL小提琴

#1