求跨MSSQL与Oracle的高效SQL查询：按id取最小group及对应列

原始数据表 #

查询需求 #

提取去重后的id，每个id对应最小的group值，同时返回该最小group对应的recno列。

期望结果 #

现有查询的局限 #

如果不需要返回recno，可以用以下语句：

SELECT id, MIN(group)
FROM your_table
WHERE group > 0
GROUP BY id;

但加入recno列后，会因聚合函数规则限制导致报错。

额外要求 #

• 目标表数据量超50万条，需保证查询效率
• 需兼容MSSQL和Oracle（优先遵循ANSI 92标准，分库实现也可）
• 仅允许使用SQL语句，禁止存储过程

方法一：ANSI标准窗口函数（双库兼容） #

使用ROW_NUMBER()窗口函数，按id分组后，以group升序、recno升序排序，取每组第一条记录：

SELECT id, "group", recno
FROM (
    SELECT 
        id, 
        "group", 
        recno,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY "group" ASC, recno ASC) AS rn
    FROM your_table
    WHERE "group" > 0
) t
WHERE rn = 1;

优化说明 #

• 建议在表上创建复合索引(id, "group", recno)，让数据库能直接通过索引完成排序和筛选，大幅提升大数据量下的查询速度
• 若同一id和最小group存在多条记录，该语句会取recno最小的那条，与期望结果完全匹配

方法二：关联子查询（ANSI兼容） #

先通过子查询获取每个id的最小group，再关联原表筛选对应记录，并取该组内最小的recno：

SELECT t.id, t."group", t.recno
FROM your_table t
INNER JOIN (
    SELECT id, MIN("group") AS min_group
    FROM your_table
    WHERE "group" > 0
    GROUP BY id
) g ON t.id = g.id AND t."group" = g.min_group
WHERE t.recno = (
    SELECT MIN(recno) 
    FROM your_table 
    WHERE id = t.id AND "group" = g.min_group
);

优化说明 #

• 同样需要创建(id, "group", recno)复合索引来保障性能，避免全表扫描

数据库专属优化方案（可选） #

Oracle 专属写法 #

利用KEEP子句直接在聚合查询中获取对应recno：

SELECT 
    id,
    MIN("group") AS "group",
    MIN(recno) KEEP (DENSE_RANK FIRST ORDER BY "group" ASC) AS recno
FROM your_table
WHERE "group" > 0
GROUP BY id;

MSSQL 专属写法 #

使用TOP 1 WITH TIES简化窗口函数写法：

SELECT TOP 1 WITH TIES
    id, [group], recno
FROM your_table
WHERE [group] > 0
ORDER BY ROW_NUMBER() OVER (PARTITION BY id ORDER BY [group] ASC, recno ASC);

内容的提问来源于stack exchange，提问作者Mick Gibbin