sql-server - SQL-获取具有最大值的列的索引

Question

我有一个包含示例数据的表格，如下所示。

col1    col2    col3
4       6       9
7       1       5

我想获取值与该行上的最大值匹配的列的索引，如果它们相等，则忽略后面的值。

例如，结果应该是return

3 (because col3 has maximum value 9)
1 (because col1 has maximum value 7)

请注意，列数未定义，因此我需要一个通用的解决方案。

谢谢

Answer 1

对此的更通用的解决方案(即 N 列)是将列逆透视为行，然后可以应用窗口函数来获取每组列“行”的分组最大值。然而，每行都需要某种类型的键，以便可以以行方式应用最大值(以允许重新组装原始行)。我通过 newId() 添加代理 Guid 来完成此操作。请注意，这将返回每行中具有最高值的列 NAME:

WITH MyTableWithRowId AS
(
    SELECT newId() AS Id, *
    FROM MyTable
),
Unpivoted AS
(
    SELECT Ndx, Id, col, ROW_NUMBER() OVER (PARTITION BY Id ORDER BY col DESC) AS Rnk
    FROM 
    MyTableWithRowId tbl
    UNPIVOT
    (
      col for Ndx in(col1, col2, col3)
    ) p
)
SELECT Ndx
FROM Unpivoted
WHERE Rnk = 1

SqlFiddle here

编辑，只是“1,2,3”而不是列的名称(col1,col2,col3)

根据 @Giorgi 的评论，如果您确实想要每行中列的(基于一个的)序数位置，您可以连接回 DMV，例如 INFORMATION_SCHEMA.COLUMNS 来查找序数，尽管在我看来，这将是一个非常脆弱的策略。

WITH MyTableWithRowId AS
(
    SELECT newId() AS Id, col1, col2, col3
    FROM MyTable
),
TheOrdinalPositionOfColumns AS
(
    SELECT COLUMN_NAME, ORDINAL_POSITION
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'MyTable'
),
Unpivoted AS
(
    SELECT Ndx, Id, col, ROW_NUMBER() OVER (PARTITION BY Id ORDER BY col DESC) AS Rnk
    FROM 
    MyTableWithRowId tbl
    UNPIVOT
    (
      col for Ndx in(col1, col2, col3)
    ) p
)
SELECT topoc.ORDINAL_POSITION AS ColumnOrdinalPosition
FROM Unpivoted
JOIN TheOrdinalPositionOfColumns topoc ON Unpivoted.Ndx = topoc.COLUMN_NAME
WHERE Rnk = 1;

Updated Fiddle with Giorgi's Column naming