如何按一列的最大值获取SQL行，并按另一列分组

Question

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

该表具有以下相关列：（统计列在此不相关）

• UserID
• FirstName
• LastName
• Email
• Year

我希望 UserID 是不同的，因此它是我可以在 GROUP BY 中拥有的唯一列。 我将在 Year 上运行 MAX，以获取最近一年的值。 名字、姓氏和电子邮件需要与 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 实际上是否安全？

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

Answer 2

每个用户只有一年的记录吗？如果是，那么您可以使用旧的'n'good 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 版本的构造，我只是已经习惯了较旧的（=更通用的）可能性:)。