Oracle中按供应编码统计供应商类型并取数量最高值的需求

咱们先梳理下你的需求：针对每个supply_code，统计两种supplier_type的数量，最终返回每个编码下数量最多的类型。先看看你现有SQL的问题，再一步步给出正确实现方案：

现有SQL的问题 #

你的初步语句存在几个关键问题：

• 表名拼写错误：supplier table应该是supplier_table
• 缺少核心的分组统计步骤：原表没有count_of_supplier字段，需要先按supply_code和supplier_type分组计算数量
• 逻辑未关联单个供应编码：直接取全局的最大数量，无法实现每个编码单独统计最常见类型

正确实现方案（窗口函数版） #

在Oracle中，使用窗口函数是最简洁高效的解决方式，既能清晰统计数量，又能精准筛选每个编码的Top类型。

方案1：处理无并列的情况（仅返回一个Top类型） #

如果业务中不存在同一编码下两种类型数量相等的情况，或者需要在并列时按类型名称优先级返回，用ROW_NUMBER()：

WITH type_counts AS (
    -- 第一步：统计每个供应编码下各类型的数量
    SELECT 
        S_CODE AS supply_code,
        S_TYPE AS supplier_type,
        COUNT(*) AS type_count
    FROM supplier_table
    GROUP BY S_CODE, S_TYPE
),
ranked_types AS (
    -- 第二步：给每个供应编码下的类型按数量排序，并列时按类型名称排序
    SELECT 
        supply_code,
        supplier_type,
        type_count,
        ROW_NUMBER() OVER (
            PARTITION BY supply_code 
            ORDER BY type_count DESC, supplier_type
        ) AS rn
    FROM type_counts
)
-- 第三步：筛选出每个编码下排名第一的类型
SELECT supply_code, supplier_type AS most_common_supplier_type
FROM ranked_types
WHERE rn = 1;

方案2：支持并列情况（返回所有数量最多的类型） #

如果允许同一编码下多个类型数量并列最多，用RANK()替代ROW_NUMBER()：

WITH type_counts AS (
    SELECT 
        S_CODE AS supply_code,
        S_TYPE AS supplier_type,
        COUNT(*) AS type_count
    FROM supplier_table
    GROUP BY S_CODE, S_TYPE
),
ranked_types AS (
    SELECT 
        supply_code,
        supplier_type,
        type_count,
        RANK() OVER (
            PARTITION BY supply_code 
            ORDER BY type_count DESC
        ) AS rk
    FROM type_counts
)
SELECT supply_code, supplier_type AS most_common_supplier_type
FROM ranked_types
WHERE rk = 1;

简化版（无需CTE） #

如果觉得CTE繁琐，也可以用子查询嵌套实现：

SELECT supply_code, supplier_type AS most_common_supplier_type
FROM (
    SELECT 
        S_CODE AS supply_code,
        S_TYPE AS supplier_type,
        COUNT(*) AS type_count,
        ROW_NUMBER() OVER (
            PARTITION BY S_CODE 
            ORDER BY COUNT(*) DESC, S_TYPE
        ) AS rn
    FROM supplier_table
    GROUP BY S_CODE, S_TYPE
) t
WHERE rn = 1;

示例数据执行结果 #

针对你提供的示例数据，上述SQL会返回：

完全符合你的需求。

内容的提问来源于stack exchange，提问作者Yoga priya Duraipandian