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

Question

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

6

我有一张包含以下示例数据的表格。

col1    col2    col3
4       6       9
7       1       5

我希望能够获取具有最大值匹配的列的索引，如果它们相等，则忽略后面的列。

例如，结果应该返回：

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

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

- YukiSakura

如果它们相等，你想要什么？ - Giorgi Nakeuri

如果它们相等，只需忽略后面的内容。 - YukiSakura

7个回答

3

一种更通用的解决方案（即N列）是将列转换为行，然后应用窗口函数以获取每组列“行”中的分组最大值。但是，您需要为每一行提供某种键，以便可以以行方式应用最大值（以允许重新组装原始行）。我通过添加一个代理Guid（通过newId()）来完成这个过程。请注意，这将返回每行中具有最高值的列名称：

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

编辑，只需使用“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

- StuartLC

1

如果列名是：SomeCol OtherCol WhatCol，则此方法无效。http://sqlfiddle.com/#!6/4b7bc/1 - Giorgi Nakeuri

你的答案看起来不错。不过我只需要索引（1而不是col1）。你能修改一下你的回答吗？ - YukiSakura

@YukiSakura 我有 - 第二个查询将返回列的序数。您需要在创建表时防止DDL更改，并防止对列结构进行DDL修改。 - StuartLC

@StuartLC：我访问了SQL fiddle的示例。结果是1, 3，但应该是3, 1。有什么问题吗？ - YukiSakura

@StuartLC：我的表已经有自增列了。我没有发布它只是为了让示例简单。这是真实情况：我有一个包括自增列在内的7列表。我创建了一个存储过程，在运行时添加N个名为“Extra1”、“Extra2”...“ExtraN”的列。这些N列被更新为值。我想找到这些N列中的最大值，然后返回索引1-N。你知道我们是否可以获取列名末尾的数字，“Extra1”-> 1，“Extra34”-> 34吗？我认为你可以改进你的第一个答案。 - YukiSakura

显示剩余2条评论

2

尝试像这样做

select 
case when col1 >= col2 and col1 >= col3 then 1
     when col2 >= col1 and col2 >= col3 then 2
  else 3 end as [index]
from myquestion_table

查看一个演示在这里

- Rahul

1

我认为，根据OP最新的评论如果它们相等，只需忽略后一个，您应该将>更改为> =以选择第一列。 - Giorgi Nakeuri

@GiorgiNakeuri，感谢您指出这一点；顺便问一下，OP什么时候发表评论的...我没看到。 - Rahul

是的，我现在明白了；已经编辑了问题以包含该点。再次感谢。 - Rahul

1

抱歉，我编辑了我的问题，包括“未定义的列数”。你能修改你的答案吗？ - YukiSakura

2

尝试这个，不用枢轴。

-- 你可以添加任意数量的列。

CREATE TABLE Table1
    ([id] int primary key identity(1,1),[col1] int, [col2] int, [col3] int)
;

INSERT INTO Table1
    ([col1], [col2], [col3])
VALUES
    (4, 6, 9),
    (7, 1, 5)
;

DECLARE @tempTable as table(name varchar(50),maxValue int)


DECLARE @maxColumn int
SELECT @maxColumn = max(ordinal_position)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Table1'

DECLARE @maxRow int
SELECT @maxRow = Count(col1) FROM Table1


DECLARE @rowCounter int = 1
DECLARE @colCounter int = 1

DECLARE @columnName varchar(max)
DECLARE @colValue varchar(max)
DECLARE @q nvarchar(max)


DECLARE @maxValue int
DECLARE @ParmDefinition nvarchar(500)
DECLARE @FinalResult table (id int, columnName nvarchar(max))
DECLARE @rowId int

WHILE(@rowCounter <= @maxRow)
BEGIN
    WHILE (@colCounter <= @maxColumn)
    BEGIN

        SELECT @columnName = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = N'Table1' and ordinal_position = @colCounter

        --select @columnName,@rowCounter,@colCounter

        SELECT @q = 'select @retvalOUT =' + @columnName + ' from Table1 where id = ' + cast(@rowCounter as NVARCHAR)

        SET @ParmDefinition = N'@retvalOUT int OUTPUT';

        EXEC sp_executesql  @q,@ParmDefinition ,@retvalOUT = @maxValue OUT
        --select '@maxValue' + @maxValue

        INSERT INTO @tempTable VALUES (@columnName,@maxValue)

        SET @colCounter = @colCounter + 1
    END



SELECT @rowId = maxValue FROM @tempTable WHERE name LIKE 'id' -- Primary key column

INSERT INTO @FinalResult(id,columnName)
SELECT TOP 1 @id,name FROM @tempTable WHERE name not like 'id' ORDER BY maxvalue DESC

DELETE FROM @tempTable

--select * from @FinalResult

SET @colCounter = 1
SET @rowCounter = @rowCounter + 1
END

SELECT * FROM @FinalResult

- user4947592

2

这里有另一种数据透视解决方案，比其他数据透视解决方案稍微简洁一些：

DECLARE @t table
(col1 int, col2 int, col3 int)
INSERT @t 
SELECT 4,8,9 union all SELECT 7,1,5 

;WITH addrownumber AS
(
  SELECT 
    rn = row_number() over (order by (select 1)),
    *
  FROM @t
)
, unpiv AS
(
  SELECT 
    rn, 
    value, 
    colname, 
    ordinalposition = row_number() over (partition by rn order by (select 1)),
    rn2 = row_number() over (partition by rn order by value DESC, rn)
  FROM addrownumber as p  
  UNPIVOT
  (value FOR colname IN           
  ([col1], [col2], [col3])) AS unpvt  
  -- since you need all columns to be mentioned in pivot, you can set up
  --  the ordinal order here, by putting in columns in the right order.
)
SELECT ordinalposition, value, colname
FROM unpiv
WHERE rn2 = 1

结果：

ordinalposition value   colname
3               9       col3
1               7       col1

- t-clausen.dk

1

试试这个：

select case 
     when col1 >= col2 and col1 >= col3 then 1
     when col2 >= col1 and col2 >= col3 then 2
     else 3
     end as ind
from mytable

- Rahul Tripathi

1

这只是一个非常基本的例子，但它可能是这样的:

select case when col1 > col2 and col1 > col3 then col1
       when col2> col3 then col2
       else col3 end as greatestColumn
   from table

- JohnandLyn Henry

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Giorgi Nakeuri · Accepted Answer

你可以这样做：

select case 
           when col1 >= col2 and col1 >= col3 then 1
           when col2 >= col1 and col2 >= col3 then 2
           when col3 >= col1 and col3 >= col2 then 3
        end as ColIndex
from table