T-SQL:基于MAX(其他列)选择列

2021-12-10 00:00:00 sql tsql sql-server greatest-n-per-group

我希望有一种不使用子查询的简单方法:

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

场景:您有TableA"，其中包含Key"、SubKey"和Value"列.我需要获取给定密钥"的 MAX(子密钥")的值".

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

所以如果表包含行:

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

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

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

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

但那是不行的.有没有办法在不做WHERE SubKey = (subselect for max subkey)"的情况下做到这一点?

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

推荐答案

使用自联接:

这将返回所有子键值匹配的值，以防有多个.

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

使用 RANK &CTE(SQL Server 2005+):

这将返回所有子键值匹配的值，以防有多个.

Using 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

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

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

Using 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

相关文章