在PostgreSQL中使用DISTINCT选择列 [英] Selecting columns with DISTINCT in PostgreSQL

问题描述

我正在从数据库中查询公交车站，但我希望每个公交线路/方向仅返回1站。这个查询就是这样：

I'm querying bus stops from a database, and I wish to have it only return 1 stop per bus line/direction. This query does just that:

Stop.select("DISTINCT line_id, direction")

除了它不会为我提供任何其他属性（2）。我尝试了几次其他查询，使它返回 id 和 line_id 和 direction 字段（理想情况下，它将返回所有列），没有运气：

Except that it won't give me any other attribute than those 2. I tried a couple of other queries to have it return the id in addition to the line_id and direction fields (ideally it would return all columns), with no luck:

Stop.select("DISTINCT line_id, direction, id")

和

Stop.select("DISTINCT(line_id || '-' || direction), id")

在这两种情况下，查询都将丢失其独特的子句，并返回所有行。

In both cases, the query loses its distinct clause and all rows are returned.

一些很棒的家伙帮助了我，并建议使用子查询使它返回所有id：

Some awesome dude helped me out and suggested to use a subquery to have it return all the ids:

Stop.find_by_sql("SELECT DISTINCT a1.line_id, a1.direction, (SELECT a2.id from stops a2 where a2.line_id = a1.line_id AND a2.direction = a1.direction ORDER BY a2.id ASC LIMIT 1) as id FROM stops a1

然后我可以提取所有id并执行

I can then extract all the ids and perform a 2nd query to fetch the full attributes for each stop.

有没有一种方法可以将所有内容都包含在1个查询中并返回所有属性？

Is there a way to have it all inside 1 query AND have it return all the attributes?

推荐答案

Stop.select("DISTINCT ON (line_id, direction) *")

这篇关于在PostgreSQL中使用DISTINCT选择列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！