MySQL技术求助：如何获取各城市订单量最多/最少的客户？

原查询的问题 #

你的SQL存在两个核心问题：

• 外层查询未按City分组，导致max(no_orders)和min(no_orders)是全局范围内的订单数最值，而非每个城市单独的最值
• Customer_Name既未被聚合函数包裹，也不在GROUP BY子句中，在严格SQL模式下会直接报错；即使不报错，返回的客户名也是随机的，无法对应到目标城市的最值客户

正确查询方案 #

方案1：用窗口函数（推荐，适用于MySQL 8+、PostgreSQL、SQL Server等） #

通过窗口函数标记每个城市内客户订单数的排名，直接筛选出排名第一（最多）和倒数第一（最少）的客户：

WITH customer_order_stats AS (
    SELECT 
        Customer_Name,
        City,
        COUNT(DISTINCT Order_ID) AS no_orders
    FROM superstore
    GROUP BY City, Customer_Name
),
ranked_customers AS (
    SELECT
        Customer_Name,
        City,
        no_orders,
        -- 按城市分区，订单数降序排名（1是最多）
        RANK() OVER (PARTITION BY City ORDER BY no_orders DESC) AS top_rank,
        -- 按城市分区，订单数升序排名（1是最少）
        RANK() OVER (PARTITION BY City ORDER BY no_orders ASC) AS bottom_rank
    FROM customer_order_stats
)
SELECT
    City,
    Customer_Name,
    no_orders,
    CASE 
        WHEN top_rank = 1 THEN '订单数最多'
        WHEN bottom_rank = 1 THEN '订单数最少'
    END AS customer_type
FROM ranked_customers
WHERE top_rank = 1 OR bottom_rank = 1
ORDER BY City, customer_type;

注：RANK()会保留并列排名（比如两个客户订单数同为最多，都会被标记为1），如果需要每个城市只返回一个客户，可替换为ROW_NUMBER()（随机取并列中的一个）。

方案2：关联子查询（兼容旧版数据库） #

先计算每个城市的订单数最值，再关联回客户统计结果，找出对应客户：

SELECT
    city_stats.City,
    -- 用GROUP_CONCAT拼接并列的客户名，可根据数据库替换为STRING_AGG等函数
    GROUP_CONCAT(DISTINCT top_customers.Customer_Name) AS 订单数最多客户,
    city_stats.max_orders AS 最多订单数,
    GROUP_CONCAT(DISTINCT bottom_customers.Customer_Name) AS 订单数最少客户,
    city_stats.min_orders AS 最少订单数
FROM (
    SELECT
        City,
        MAX(no_orders) AS max_orders,
        MIN(no_orders) AS min_orders
    FROM (
        SELECT
            City,
            Customer_Name,
            COUNT(DISTINCT Order_ID) AS no_orders
        FROM superstore
        GROUP BY City, Customer_Name
    ) AS customer_stats
    GROUP BY City
) AS city_stats
LEFT JOIN (
    SELECT
        City,
        Customer_Name,
        COUNT(DISTINCT Order_ID) AS no_orders
    FROM superstore
    GROUP BY City, Customer_Name
) AS top_customers
    ON city_stats.City = top_customers.City 
    AND city_stats.max_orders = top_customers.no_orders
LEFT JOIN (
    SELECT
        City,
        Customer_Name,
        COUNT(DISTINCT Order_ID) AS no_orders
    FROM superstore
    GROUP BY City, Customer_Name
) AS bottom_customers
    ON city_stats.City = bottom_customers.City 
    AND city_stats.min_orders = bottom_customers.no_orders
GROUP BY city_stats.City, city_stats.max_orders, city_stats.min_orders
ORDER BY city_stats.City;

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