SQL 如何在包含最大值的表中查找记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/376518/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to find the record in a table that contains the maximum value?
提问by Jonas
Although this question looks simple, it is kind of tricky.
虽然这个问题看起来很简单，但它有点棘手。
I have a table with the following columns:
我有一个包含以下列的表格：
table A:
int ID
float value
datetime date
varchar(50) group
I would like to obtain the "ID" and "value" of the records that contain the maximum "date" grouped by the column "group". Something like "what is the newest value for each group?"
我想获取包含按“组”列分组的最大“日期”的记录的“ID”和“值”。类似于“每个组的最新值是多少？”
I can get each group and its maximum date:
我可以获得每个组及其最大日期：
SELECT group, MAX(date) FROM A GROUP BY group; -- I also need the "ID" and "value"
SELECT group, MAX(date) FROM A GROUP BY group; -- I also need the "ID" and "value"
But I would like to have the "ID" and value of the record with the highest date.
但我想拥有最高日期的记录的“ID”和值。
Making a JOIN between A and the result could be the answer, but there is no way of knowing which record MAX(date) refers to (in case the "date" repeats).
在 A 和结果之间进行 JOIN 可能是答案，但无法知道 MAX(date) 指的是哪条记录（以防“日期”重复）。
Can someone help?
有人可以帮忙吗？
回答by Vinko Vrsalovic
You could try with a subquery
您可以尝试使用子查询
select group, id, value, date from A where date in ( select MAX(date) as date from A group by group ) order by group
回答by David Aldridge
This is just what analytic functions were made for:
这正是分析函数的用途：
select group,
id,
value
from (
select group,
id,
value,
date,
max(date) over (partition by group) max_date_by_group
from A
)
where date = max_date_by_group
回答by Mark Brackett
If date is unique, then you already have your answer. If date is not unique, then you need some other uniqueifier. Absent a natural key, your ID is as good as any. Just put a MAX (or MIN, whichever you prefer) on it:
如果日期是唯一的，那么您已经有了答案。如果日期不是唯一的，那么您需要一些其他的唯一标识符。如果没有自然密钥，您的 ID 与任何 ID 一样好。只需在其上放置一个 MAX（或 MIN，无论您喜欢哪个）：
SELECT *
FROM A
JOIN (
--Dedupe any non unqiue dates by getting the max id for each group that has the max date
SELECT Group, MAX(Id) as Id
FROM A
JOIN (
--Get max date for each group
SELECT group, MAX(date) as Date
FROM A
GROUP BY group
) as MaxDate ON
A.Group = MaxDate.Group
AND A.Date = MaxDate.Date
GROUP BY Group
) as MaxId ON
A.Group = MaxId.Group
AND A.Id= MaxId.Id
回答by Andrew Kennan
As long as the Date column is unique for each group I think something like this might work:
只要 Date 列对于每个组都是唯一的，我认为这样的事情可能会起作用：
SELECT A.ID, A.Value
FROM A
INNER JOIN (SELECT Group, MAX(Date) As MaxDate FROM A GROUP BY Group) B
ON A.Group = B.Group AND A.Date = B.MaxDate
