sql - PostgreSQL:根据排序顺序仅选择每个 id 的第一条记录

Question

对于以下查询，我只需要选择具有最低 shape_type 值(范围从 1 到 10)的第一条记录。如果您对如何轻松执行此操作有任何了解，请使用 postgresql，请提供帮助。谢谢你的时间。

select g.geo_id, gs.shape_type
from schema.geo g   
join schema.geo_shape gs on (g.geo_id=gs.geo_id)  
order by gs.shape_type asc;

Answer 1

PostgreSQL 对这类查询有很好的语法 - distinct on :

SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first.

因此您的查询变为:

select distinct on(g.geo_id)
    g.geo_id, gs.shape_type
from schema.geo g   
    join schema.geo_shape gs on (g.geo_id=gs.geo_id)  
order by g.geo_id, gs.shape_type asc;

一般来说，这个的 ANSI-SQL 语法(在任何具有窗口函数和公共(public)表表达式的 RDBMS 中，可以切换到子查询)是:

with cte as (
    select
        row_number() over(partition by g.geo_id order by gs.shape_type) as rn,
        g.geo_id, gs.shape_type
    from schema.geo g   
        join schema.geo_shape gs on (g.geo_id=gs.geo_id)  
)
select
    geo_id, shape_type
from cte
where rn = 1