T-SQL：基于MAX（其他列）选择列

T-SQL: Selecting Column Based on MAX(Other Column)

提问by John

I'm hoping there's a simple way to do this without using a sub-query:

我希望有一种简单的方法可以在不使用子查询的情况下做到这一点：

Scenario: You have "TableA" with columns "Key", "SubKey", and "Value". I need to get the "Value" of the MAX("SubKey") for a given "Key".

场景：您有“TableA”，其中包含“Key”、“SubKey”和“Value”列。我需要获取给定“密钥”的 MAX（“子密钥”）的“值”。

So if the Table contained the rows:

因此，如果表包含行：

KEY SUBKEY VALUE
1   1      100
1   2      200
1   3      300

For Key = 1, I need the value 300. I was hoping to do something like this:

对于 Key = 1，我需要值 300。我希望做这样的事情：

SELECT
  VALUE
FROM
  TableA
WHERE
  Key = 1
HAVING
  SubKey = MAX(SubKey)

But that's a no-go. Is there a way to do this without doing a 'WHERE SubKey = (subselect for max subkey)'?

但那是不行的。有没有办法在不执行“WHERE SubKey =（最大子键的子选择）”的情况下做到这一点？

回答by OMG Ponies

Using a self join:

使用自连接：

This will return all the values with subkey values that match, in case there are multiples.

这将返回具有匹配的子键值的所有值，以防有倍数。

SELECT a.value
  FROM TABLE a
  JOIN (SELECT MAX(t.subkey) AS max_subkey
          FROM TABLE t
         WHERE t.key = 1) b ON b.max_subkey = a.subkey
 WHERE a.key = 1

Using RANK & CTE (SQL Server 2005+):

使用 RANK 和 CTE（SQL Server 2005+）：

This will return all the values with subkey values that match, in case there are multiples.

这将返回具有匹配的子键值的所有值，以防有倍数。

WITH summary AS (
  SELECT t.*,
         RANK() OVER(ORDER BY t.subkey DESC) AS rank
    FROM TABLE t
   WHERE t.key = 1)
SELECT s.value
  FROM summary s
 WHERE s.rank = 1

Using ROW_NUMBER & CTE (SQL Server 2005+):

使用 ROW_NUMBER & CTE (SQL Server 2005+)：

This will return one row, even if there are more than one with the same subkey value...

这将返回一行，即使有多个具有相同子键值的...

WITH summary AS (
  SELECT t.*,
         ROW_NUMBER() OVER(ORDER BY t.subkey DESC) AS rank
    FROM TABLE t
   WHERE t.key = 1)
SELECT s.value
  FROM summary s
 WHERE s.rank = 1

Using TOP:

使用顶部：

This will return one row, even if there are more than one with the same subkey value...

这将返回一行，即使有多个具有相同子键值的...

  SELECT TOP 1
         t.value
    FROM TABLE t
   WHERE t.key = 1
ORDER BY t.subkey DESC

回答by Nguyen Viet Cuong

Very simple, no join, no sub-query:

很简单，没有join，没有子查询：

SELECT FIRST_VALUE(Value) OVER (ORDER BY SubKey DESC)
FROM TableA
WHERE Key = 1

If you need max value for each Key:

如果您需要每个键的最大值：

SELECT DISTINCT Key, 
FIRST_VALUE(Value) OVER (PARTITION BY Key ORDER BY SubKey DESC)
FROM TableA

回答by Amy B

SELECT MAX(Value)
FROM TableA t1
GROUP BY Key, SubKey
HAVING SubKey = (SELECT MAX(SubKey) FROM TableA t2 WHERE t1.Key = t2.Key)
  AND Key = 1

回答by Tom H

OMG Ponies hit most of the ways to do it. Here's one more:

OMG Ponies 找到了大多数方法。还有一个：

SELECT
    T1.value
FROM
    My_Table T1
LEFT OUTER JOIN My_Table T2 ON
    T2.key = T1.key AND
    T2.subkey > T1.subkey
WHERE
    T2.key IS NULL

The only time that T2.key will be NULL is when there is no match in the LEFT JOIN, which means that no row exists with a higher subkey. This will return multiple rows if there are multiple rows with the same (highest) subkey.

T2.key 为 NULL 的唯一时间是在 LEFT JOIN 中没有匹配项时，这意味着不存在具有更高子键的行。如果有多行具有相同（最高）子键，这将返回多行。

回答by Diego

OMG Ponie's ROW_NUMBERmethod is the one that will work best in all scenarios as it will not fail in the event of having two MAXvalues with the same amount returning more records than expected and breaking a possible insert you might have being fed by that recordset.

OMG Ponie的ROW_NUMBER方法是最适合所有场景的方法，因为如果有两个MAX相同数量的值返回比预期多的记录并破坏可能的插入，则它不会失败recordset。

One thing that is missing is how to do it in the event of having to return the subkey associated to each max value, when there are also multiple keys. Simply join your summarytable with a MINand GROUP"itself" and off you go.

缺少的一件事是在必须返回与每个最大值关联的子键的情况下如何做到这一点，同时还有多个键。只需summary用 aMIN和GROUP“本身”加入您的桌子，然后就可以了。

WITH summary AS (
  SELECT t.*,
         ROW_NUMBER() OVER(ORDER BY t.subkey DESC) AS rank
    FROM TABLE t
   WHERE t.key = 1)
SELECT s.*
  FROM summary s
  join  (select key, min(rank) as rank
        from summary
        group by key) sMAX
        on s.key = sMAX.key and r.rank = sMAX.rank

回答by ErikE

If you'll always want just one row for one key value rather than the answer for many keys at once, all the join stuff is useless overbuilding. Just use the TOP 1 query OMG Ponies already gave you.

如果您总是只想要一行一个键值而不是一次多个键的答案，那么所有连接的东西都是无用的过度构建。只需使用 OMG Ponies 已经给你的 TOP 1 查询。

回答by Glen

In case of multiple keys using a CTE:

在使用 CTE 的多个键的情况下：

WITH CTE AS
(
    SELECT key1, key2, MAX(subkey) AS MaxSubkey
    FROM TableA 
    GROUP BY key1, key2
)
SELECT a.Key1, a.Key2, a.Value
FROM TableA a
    INNER JOIN CTE ON a.key1 = CTE.key1 AND a.key2 = CTE.key2 AND
                      a.subkey = CTE.MaxSubkey