Oracle SQL查询需求：获取销量最高车型及对应购车客户

问题分析 #

你现在的需求是从CUSTOMERS、VEHICLES、SALES这三张关联表中，找出销量最高的车辆品牌，同时列出所有购买该品牌的客户信息，还要支持多个品牌并列第一的场景，另外还要解决之前查询里重复行、冗余列的问题。

解决方案思路 #

我分步骤拆解逻辑，先算出每个品牌的销量、锁定最高销量的品牌，再关联对应的客户信息，最后用合适的方式格式化输出，满足你“品牌+销量只显示一次，下方列客户姓名”的要求。这里提供两种方案，你可以根据实际场景选择：

方案1：紧凑式输出（品牌、销量+拼接的客户列表） #

这种方式把客户姓名用逗号拼接在同一行，适合需要简洁报表的场景：

WITH brand_sales AS (
    -- 第一步：统计每个品牌的已售车辆数量
    SELECT 
        v.vehicleMake,
        COUNT(s.saleID) AS total_sales
    FROM VEHICLES v
    JOIN SALES s ON v.vehicleVIN = s.vehicleVIN
    WHERE s.vehicleStatus = 'sold' -- 只统计已售出的订单
    GROUP BY v.vehicleMake
),
top_brands AS (
    -- 第二步：筛选出销量等于最高值的品牌（支持并列第一）
    SELECT vehicleMake, total_sales
    FROM brand_sales
    WHERE total_sales = (SELECT MAX(total_sales) FROM brand_sales)
)
-- 第三步：关联品牌、销售记录和客户信息，拼接客户姓名
SELECT 
    tb.vehicleMake AS 车辆品牌,
    tb.total_sales AS 总销量,
    LISTAGG(c.customerFirName || ' ' || c.customerLasName, ', ') 
        WITHIN GROUP (ORDER BY c.customerFirName) AS 购车客户列表
FROM top_brands tb
JOIN VEHICLES v ON tb.vehicleMake = v.vehicleMake
JOIN SALES s ON v.vehicleVIN = s.vehicleVIN
JOIN CUSTOMERS c ON s.customerID = c.customerID
WHERE s.vehicleStatus = 'sold'
GROUP BY tb.vehicleMake, tb.total_sales
ORDER BY tb.total_sales DESC, tb.vehicleMake;

方案2：分层式输出（品牌+销量单独一行，客户在下方缩进显示） #

这种完全贴合你描述的“品牌销量各显一次，下方列客户”的需求，用Oracle的层级逻辑实现格式化：

WITH brand_sales AS (
    SELECT 
        v.vehicleMake,
        COUNT(s.saleID) AS total_sales
    FROM VEHICLES v
    JOIN SALES s ON v.vehicleVIN = s.vehicleVIN
    WHERE s.vehicleStatus = 'sold'
    GROUP BY v.vehicleMake
),
top_brands AS (
    SELECT vehicleMake, total_sales
    FROM brand_sales
    WHERE total_sales = (SELECT MAX(total_sales) FROM brand_sales)
),
customer_data AS (
    -- 生成品牌行（层级1）和客户行（层级2）
    SELECT 
        tb.vehicleMake,
        tb.total_sales,
        c.customerFirName || ' ' || c.customerLasName AS customer_name,
        2 AS line_level, -- 客户行层级
        ROW_NUMBER() OVER (PARTITION BY tb.vehicleMake ORDER BY c.customerFirName) AS cust_seq
    FROM top_brands tb
    JOIN VEHICLES v ON tb.vehicleMake = v.vehicleMake
    JOIN SALES s ON v.vehicleVIN = s.vehicleVIN
    JOIN CUSTOMERS c ON s.customerID = c.customerID
    WHERE s.vehicleStatus = 'sold'
    UNION ALL
    SELECT 
        vehicleMake,
        total_sales,
        NULL,
        1 AS line_level, -- 品牌信息行层级
        0 AS cust_seq
    FROM top_brands tb
)
-- 格式化输出，品牌行在前，客户行缩进显示
SELECT 
    CASE 
        WHEN line_level = 1 THEN '品牌: ' || vehicleMake || ' | 总销量: ' || total_sales
        ELSE '  → ' || customer_name
    END AS 输出内容
FROM customer_data
ORDER BY vehicleMake, line_level, cust_seq;

关键细节说明 #

• 用CTE（公共表表达式）分步处理，逻辑清晰，避免了嵌套子查询的混乱；
• 加入WHERE s.vehicleStatus = 'sold'确保只统计真正售出的车辆，符合“销量”的定义；
• 方案2的输出格式示例：

  品牌: Toyota | 总销量: 15
    → John Doe
    → Jane Smith
  品牌: Honda | 总销量: 15
    → Mike Johnson
  

• 自动支持多品牌并列第一的场景，只要品牌销量等于最高值，都会被纳入结果；
• 解决了重复行问题：通过分组和正确的关联条件，避免了表关联时产生的笛卡尔积。

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