Oracle SQL Developer按客户分类汇总月度销量及CASE语句问题咨询

方案一：直接在分组中使用CASE逻辑 #

SELECT
    year,
    month,
    CASE
        WHEN customer_name LIKE '%Farming company%' THEN 'Farming company'
        WHEN customer_name LIKE '%XY Company%' THEN 'XY Company'
        WHEN customer_name LIKE '%Hello Company%' THEN 'Hello Company'
        ELSE 'Other'
    END AS grouped_customer,
    SUM(sales_volume) AS total_sales
FROM
    your_table_name  -- 替换为你的实际表名
GROUP BY
    year,
    month,
    CASE
        WHEN customer_name LIKE '%Farming company%' THEN 'Farming company'
        WHEN customer_name LIKE '%XY Company%' THEN 'XY Company'
        WHEN customer_name LIKE '%Hello Company%' THEN 'Hello Company'
        ELSE 'Other'
    END
ORDER BY
    year,
    month,
    grouped_customer;

方案二：用子查询预分组客户（更易读） #

WITH grouped_customers AS (
    SELECT
        year,
        month,
        sales_volume,
        CASE
            WHEN customer_name LIKE '%Farming company%' THEN 'Farming company'
            WHEN customer_name LIKE '%XY Company%' THEN 'XY Company'
            WHEN customer_name LIKE '%Hello Company%' THEN 'Hello Company'
            ELSE 'Other'
        END AS grouped_customer
    FROM
        your_table_name  -- 替换为你的实际表名
)
SELECT
    year,
    month,
    grouped_customer,
    SUM(sales_volume) AS total_sales
FROM
    grouped_customers
GROUP BY
    year,
    month,
    grouped_customer
ORDER BY
    year,
    month,
    grouped_customer;

关键说明 #

1. 通配符调整：如果分支机构的命名规则是主公司名在前（比如Farming company - North），可以把%Farming company%改成'Farming company%'；如果主公司名在后，则改成'%Farming company'，根据你的实际数据格式调整。
2. 表名替换：务必把your_table_name替换成你使用的真实表名。
3. 排序逻辑：最终结果按年份、月份、分组客户名称排序，保证数据展示有序。

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