如何在SQL中为每个子组选取单条记录？PostgreSQL及Kysely实现

系统表结构 #

CREATE TABLE entries (
  id SERIAL PRIMARY KEY,
  slug VARCHAR(50) NOT NULL
);

CREATE TABLE pronunciations (
  id SERIAL PRIMARY KEY,
  entry_id INTEGER NOT NULL,
  position INTEGER NOT NULL,
  text VARCHAR(50) NOT NULL,
  FOREIGN KEY (entry_id) REFERENCES entries (id) ON DELETE CASCADE
);

entries与pronunciations为一对多关联，每个entry下的pronunciations按position字段排序。

需求目标 #

选取100条pronunciations记录，对应100个不同的entry；每个entry仅取position值最小（通常为0）的那条；结果仅返回pronunciations的字段，后续在应用层关联entry的其他信息。

现有实现方案（WITH子句） #

WITH ranked_pronunciations AS (
    SELECT 
        p.id,
        p.entry_id,
        p.position,
        p.text,
        ROW_NUMBER() OVER (PARTITION BY p.entry_id ORDER BY p.position) AS rn
    FROM 
        pronunciations p
)

SELECT 
    rp.id,
    rp.entry_id,
    rp.position,
    rp.text
FROM 
    ranked_pronunciations rp
WHERE 
    rp.rn = 1
ORDER BY 
    rp.entry_id
LIMIT 100;

疑问 #

1. 该方案是否为必须选项？有没有更简洁的实现方式？
2. 如何用Node.js的Kysely查询构建器实现这类逻辑？

a) 原方案并非必须，但属于高效标准实现 #

你用WITH子句+窗口函数的写法是PostgreSQL处理「分组取Top 1」场景的标准方案，逻辑清晰且性能稳定。只要给pronunciations表建立(entry_id, position)的复合索引（CREATE INDEX idx_pron_entry_pos ON pronunciations(entry_id, position);），这个查询的执行效率会非常高。

不过确实存在更简洁的替代写法。

b) 更简便的实现方式 #

方式1：子查询替代CTE #

可以把窗口函数逻辑直接嵌入子查询，省去WITH子句，代码更紧凑：

SELECT id, entry_id, position, text
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY entry_id ORDER BY position) AS rn
    FROM pronunciations
) AS sub
WHERE rn = 1
ORDER BY entry_id
LIMIT 100;

方式2：PostgreSQL专属DISTINCT ON语法 #

PostgreSQL提供的DISTINCT ON语法专门适配这类「按列去重，取排序后第一条」的场景，写法最简洁：

SELECT DISTINCT ON (entry_id) id, entry_id, position, text
FROM pronunciations
ORDER BY entry_id, position
LIMIT 100;

注意：使用DISTINCT ON时，ORDER BY的第一个字段必须和DISTINCT ON指定的字段一致，这样PostgreSQL会按entry_id分组，自动选取每组中position最小的记录。该方案性能和窗口函数方案相当，在部分场景下表现更优，同样依赖(entry_id, position)复合索引。

Kysely代码实现 #

两种方案都可以用Kysely轻松实现：

窗口函数（子查询版）实现 #

import { Kysely, sql } from 'kysely';

async function getTopPronunciations(db: Kysely<any>) {
  return db
    .selectFrom((eb) =>
      eb.selectFrom('pronunciations')
        .selectAll()
        .select(sql`ROW_NUMBER() OVER (PARTITION BY entry_id ORDER BY position) AS rn`)
        .as('sub')
    )
    .select(['id', 'entry_id', 'position', 'text'])
    .where('rn', '=', 1)
    .orderBy('entry_id')
    .limit(100)
    .execute();
}

DISTINCT ON方案实现（更简洁） #

import { Kysely } from 'kysely';

async function getTopPronunciations(db: Kysely<any>) {
  return db
    .selectFrom('pronunciations')
    .selectDistinctOn(['entry_id'])
    .select(['id', 'entry_id', 'position', 'text'])
    .orderBy('entry_id')
    .orderBy('position')
    .limit(100)
    .execute();
}

Kysely原生支持selectDistinctOn方法，完美对应PostgreSQL的DISTINCT ON语法，代码直观易读。

内容的提问来源于stack exchange，提问作者Lance Pollard