SQL Postgres 选择所有列但按一列分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7606305/
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
Postgres select all columns but group by one column
提问by Keyo
I have a simple table with a unit_id oid, time timestamp, diag bytea. The primary key is a combination of both time and unit_id.
我有一个带有 unit_id oid、时间戳、diag bytea 的简单表。主键是 time 和 unit_id 的组合。
The idea behind this query is to get the latest row (largest timestamp) for each unique unit_id. However the rows for each unit_id with the latest time are not always returned.
此查询背后的想法是获取每个唯一 unit_id 的最新行（最大时间戳）。但是，并不总是返回每个 unit_id 的最新时间行。
I really want to group by just the unit_id, but postgres makes me use diag also, since I am selecting that.
我真的想只按 unit_id 分组，但 postgres 也让我使用 diag，因为我选择了它。
SELECT DISTINCT ON(unit_id) max(time) as time, diag, unit_id
FROM diagnostics.unit_diag_history
GROUP BY unit_id, diag
回答by mu is too short
Any time you start thinking that you want a localized GROUP BY you should start thinking about window functionsinstead.
每当您开始考虑需要本地化的 GROUP BY 时，您都应该开始考虑窗口函数。
I think you're after something like this:
我认为你在追求这样的事情：
select unit_id, time, diag
from (
select unit_id, time, diag,
rank() over (partition by unit_id order by time desc) as rank
from diagnostics.unit_diag_history
) as dt
where rank = 1
You might want to add something to the ORDER BY to consistently break ties as well but that wouldn't alter the overall technique.
您可能还想在 ORDER BY 中添加一些内容以始终打破联系，但这不会改变整体技术。
回答by SingleNegationElimination
You can join the grouped select with the original table:
您可以将分组选择与原始表连接：
SELECT d.time, d.diag, d.unit_id
FROM(
SELECT unit_id, max(time) as max_time
FROM diagnostics.unit_diag_history
GROUP BY unit_id
) s JOIN diagnostics.unit_diag_history d
ON s.unit_id = d.unit_id AND s.max_time = d.time
