代码之家 › 专栏 › 技术社区 › John

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

greatest-n-per-group tsql sql-server sql

John · 技术社区 · 15 年前

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

场景:您有“tablea”和“key”、“subkey”和“value”列。我需要得到给定“键”的max(“子键”)的“值”。

因此,如果表中包含行:

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

对于key=1,我需要值300。我希望能做这样的事:

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

但那是不允许的。有没有一种不做“Where Subkey=(Subselect for Max Subkey)”的方法?

8 回复 | 直到 7 年前

OMG Ponies 15 年前

使用自联接:

这将返回具有匹配子项值的所有值,以防出现多个值。

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

使用等级(SQL Server 2005+):

这将返回具有匹配子项值的所有值,以防出现多个值。

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

使用行编号和CTE(SQL Server 2005+):

这将返回一行,即使有多个具有相同子项值的行…

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

使用顶部:

这将返回一行,即使有多个具有相同子项值的行…

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

Nguyen Viet Cuong 10 年前

非常简单,没有连接,没有子查询:

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

如果需要每个键的最大值:

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

Amy B 15 年前

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

Tom H zenazn 15 年前

OMG小马找到了很多方法。还有一个:

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

t2.key唯一为空的时间是左联接中没有匹配项时,这意味着不存在具有更高子键的行。如果有多个行具有相同(最高)子项,则返回多行。

Community Mohan Dere 9 年前

OMG Ponie 的 ROW_NUMBER 方法是在所有场景中都能发挥最佳效果的方法,因为如果有两个方法,它不会失败。 MAX 相同数量的值返回的记录数超过预期,并破坏可能的插入 recordset .

缺少的一件事是,当有多个键时,如何在必须返回与每个max值相关联的子键的情况下执行此操作。加入你的 summary A表 MIN 和 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

momo 15 年前

如果您总是希望一个键值只有一行,而不是一次回答多个键值,那么所有的连接都是无用的过度构建。只需使用OMG已经提供给您的前1个查询。

Glen 12 年前

在使用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

benjamin berhault 7 年前

对于分组查询,要根据另一列的最大值获取一列的值,可以执行以下操作:

SELECT
        CASE 
             WHEN MAX(col_C) > 0 THEN MAX(col_D)
             ELSE NULL 
        END     AS col_D_value_when_col_C_MAX
FROM table 
GROUP BY col_A, col_B