Oracle SQL：如何按序列号提取多列的最常见值

要实现按sequence_number分组，每组仅返回一条记录并获取该组内grade和od_inch的最常见值（众数），可以通过窗口函数统计频次+筛选排名的方式修改原查询，具体如下：

完整修改后的SQL #

WITH grade_modes AS (
    -- 统计每个sequence_number下各grade的出现次数，取频次最高的grade
    SELECT 
        F.prprty,
        F.sequence_number,
        F.grade AS most_common_grade,
        ROW_NUMBER() OVER (PARTITION BY F.prprty, F.sequence_number ORDER BY COUNT(*) DESC, F.grade) AS grade_rank
    FROM table F
    WHERE F.prprty = 7000
    GROUP BY F.prprty, F.sequence_number, F.grade
),
od_modes AS (
    -- 统计每个sequence_number下各od_inch的出现次数，取频次最高的od_inch
    SELECT 
        F.prprty,
        F.sequence_number,
        F.od_inch AS most_common_od,
        ROW_NUMBER() OVER (PARTITION BY F.prprty, F.sequence_number ORDER BY COUNT(*) DESC, F.od_inch) AS od_rank
    FROM table F
    WHERE F.prprty = 7000
    GROUP BY F.prprty, F.sequence_number, F.od_inch
),
base_data AS (
    -- 获取每个sequence_number对应的基础信息（去重，避免重复行）
    SELECT DISTINCT
        W.name,
        D.prprty,
        D.sequence_number
    FROM table D
    JOIN table W ON D.prprty = W.prprty
    WHERE W.prprty = 7000
)
-- 关联基础信息和两个众数结果，取排名第一的记录
SELECT 
    bd.name,
    bd.prprty,
    bd.sequence_number,
    gm.most_common_grade,
    om.most_common_od
FROM base_data bd
JOIN grade_modes gm ON bd.prprty = gm.prprty AND bd.sequence_number = gm.sequence_number AND gm.grade_rank = 1
JOIN od_modes om ON bd.prprty = om.prprty AND bd.sequence_number = om.sequence_number AND om.od_rank = 1
ORDER BY bd.sequence_number;

逻辑说明 #

1. CTE grade_modes：按prprty和sequence_number分组，统计每个grade的出现次数，用ROW_NUMBER()给每个分组内的grade按频次降序排名，排名第一的就是该组的众数（如果有多个grade频次相同，这里默认取grade值较小的，可调整ORDER BY规则）。
2. CTE od_modes：逻辑和grade_modes一致，用于获取每个分组内od_inch的众数。
3. CTE base_data：从关联的D、W表中提取每个sequence_number的基础信息并去重，保证每个序列号仅对应一条基础记录。
4. 最终关联：将基础信息表和两个众数表关联，筛选出排名第一的众数记录，得到每个序列号的最终结果。

示例数据验证 #

针对你提供的示例数据，执行该查询后会得到预期结果：

注意事项 #

• 如果需要处理**多个众数（即多个值出现次数相同且都是最高）**的场景，可以把ROW_NUMBER()换成RANK()，这样会返回所有频次最高的记录；如果只需要任意一个，保留ROW_NUMBER()即可。
• 原查询中的item_num字段因为每个序列号对应多个item，所以在最终结果中不需要保留（如果需要可以根据需求调整，但用户要求每个序列号仅返回一条记录）。

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