如何通过一个列的最大值分组获取SQL行

Question

我需要从旧数据库中读取用户信息用于新系统的统计，但我没有原始用户表。不过有一个包含每年总和的统计表，我可以在其中找到所有需要的用户信息。此外，这也只会给我活跃的用户，这正是我需要的。

该表具有以下相关列：（这里不涉及统计列）

- UserID - FirstName - LastName - Email - Year 我希望UserID是唯一的，所以它是唯一可以在GROUP BY中使用的列。 我将在Year上运行MAX，以获取最近一年的值。 FirstName，LastName和Email需要与MAX(Year)的行相同。换句话说，在这些年里可能有人改变了姓名和电子邮件，我只想要最后一个，因为这是唯一相关的。

我最好的SQL查询建议如下：

SELECT UserID, Firstname, LastName, Email, MAX(Year) AS Year
FROM myTable
GROUP BY UserID
ORDER BY LastName, FirstName

唯一的问题是SQL Server 2008不允许我这样做，因为所有列都必须使用像MAX这样的函数或者是GROUP BY的一部分。FirstName、LastName和Email列不能在GROUP BY下，因为那会产生太多记录。似乎将MAX应用于它们中的所有列可以解决问题，但是我无法知道MAX函数实际作用于哪一列。我不确定这是否会成为一个问题，但我没有时间查看100,000行以查看是否真的存在问题。

因此，简而言之，我希望在仅对一列进行MAX操作且另一列进行GROUP BY的情况下获取整行的五列数据。是否有好的解决方案，或者在非分组行上使用MAX是否安全？

- bergenga

Answer 1

有几个答案...

相关子查询...

SELECT
  *
FROM
  myTable
WHERE
  Year = (SELECT MAX(Year) FROM myTable AS lookup WHERE lookup.UserID = myTable.UserID)

连接派生聚合...

SELECT
  *
FROM
  myTable
INNER JOIN
  (SELECT UserID, MAX(Year) AS Year FROM myTable GROUP BY UserID) AS lookup
    ON  lookup.UserID = myTable.UserID
    AND lookup.Year   = myTable.Year

使用 ROW_NUMBER() 进行排序的 CTE...

WITH
  sequenced_data AS
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY Year DESC) AS sequence_id,
    *
  FROM
    myTable
)
SELECT
  *
FROM
  sequenced_data
WHERE
  sequence_id = 1

- MatBailie

---

在第二个查询中的子查询中，您需要使用 GROUP BY UserID。 - ypercubeᵀᴹ

@ypercube - 哎呀 :) 该死的手机分散了我的注意力 :) - MatBailie

哇，这个回复速度真快。谢谢你们所有人的答案！我马上就要下班回家了，但晚上会从家里仔细研究它们。 - bergenga

谢谢！我现在尝试了一下，使用你们两个给出的示例，它可以正常工作。我曾经知道JOINs，但是已经过去半个多十年了，所以显然都忘记了。很高兴你们让我重新回到正轨。 :) - bergenga

Answer 2

您的每个用户只有一年记录吗？如果是，那么您可以使用老式 join：

SELECT m.UserID, m.Firstname, m.LastName, m.Email, m.Year
FROM myTable m
    INNER JOIN (
        SELECT UserID, MAX(Year) as Year
        FROM myTable
        GROUP BY UserID
    ) x ON x.UserID=m.UserID and x.Year=m.Year
ORDER BY m.LastName, m.FirstName

当然，你可以使用更新的SQL版本中的结构体，但我已经习惯了更旧（更通用）的可能性 :).

- Arvo

---

我不明白为什么这个答案比Dems的更好。他的答案包含了这个，并且是第一个回答的。 - Florin Ghita

@FlorinGhita - Avro可能已经开始打字了，当我还没打完。我不会因为Avro提交答案而抨击他 :) 而且，呃，我的回答中有个错别字，被 ypercube 指出来了 脸红 - MatBailie

阿尔沃首先发布了这个查询。Dems先有相关的版本，然后添加了另外两个。 - ypercubeᵀᴹ

然而，目前为止还没有一个足够好以至于可以接受 :( - JoeyC