postgresql 通过在 SQL 中选择列多列来选择列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15337728/
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
Select columns by choosing column multiple columns in SQL
提问by Vladimir S.
Imagine the following exemplary table:
想象一下以下示例表：
time name value1 value2
12:00 Hans 2 4
12:30 Hans 2 4
13:00 Hans 3 5
14:00 Peter 4 4
15:00 Peter 4 4
I want to filter by maximum time stamp and name.
我想按最大时间戳和名称过滤。
Meaning I want to get
意思是我想得到
13:00 Hans 3 5
15:00 Peter 4 4
Using select max(time),name,value1,value2 from table group by name does not work. It tells me to use an aggregate function or group by on value1 and value2 also.
按名称从表组中使用 select max(time),name,value1,value2 不起作用。它告诉我在 value1 和 value2 上也使用聚合函数或 group by。
If I group by name, value1 and value2 then I get the following result though since value1 and value2 are different for two rows of Hans:
如果我按名称、value1 和 value2 分组，那么我会得到以下结果，因为两行 Hans 的 value1 和 value2 不同：
12:30 Hans 2 4
13:00 Hans 3 5
15:00 Peter 4 4
What is the solution?
解决办法是什么？
EDIT: It is a PostgreSQL.
编辑：它是一个 PostgreSQL。
回答by Taryn
You can use a subquery to get the max(time)for each nameand then join that back to your table to get the final result:
您可以使用子查询获取max(time)for each name，然后将其连接回您的表以获得最终结果：
select t1.time,
t1.name,
t1.value1,
t1.value2
from yourtable t1
inner join
(
select max(time) MaxTime, name
from yourtable
group by name
) t2
on t1.time = t2.maxtime
and t1.name = t2.name
Depending on the database that you are using, if you can apply a windowing function like row_number()or rank(), then your query would be similar to the following:
根据您使用的数据库，如果您可以应用row_number()或 之类的窗口函数rank()，那么您的查询将类似于以下内容：
select time, name, value1, value2
from
(
select time, name, value1, value2,
rank() over(partition by name order by time desc) rn
from yourtable
) src
where rn = 1
See SQL Fiddle with Demoof both queries
请参阅SQL Fiddle 和两个查询的演示
回答by Clodoaldo Neto
With distinct onit is quite simple. SQL Fiddle
有了distinct on它很简单。SQL小提琴
select distinct on (name)
"time", name, value1, value2
from t
order by t.name, t."time" desc
