如何添加子查询按销售人员分组统计对应报价的总销售额

嘿，我来帮你搞定这个问题！从你给出的SQL片段来看，你已经在统计每位销售人员的报价状态分布、报价金额和成本了，现在要补充和他们报价相关的总销售额，对吧？

首先得明确：总销售额一般来自已转化为订单的报价对应的实际成交金额，所以我们需要关联订单表（假设你的订单表叫SAL_ORDERS，通过SAL_QUOTE_ID和报价表关联，且订单里有实际销售额字段比如TOTAL_SALES_AMOUNT）。下面给你两种可行的方案：

方案1：SELECT中嵌入关联子查询 #

这种方式写法直观，适合数据量不大的场景：

SELECT 
    sp.FIRST_NAME + ' ' + sp.LAST_NAME AS [Salesperson],
    SUM(CASE WHEN sq.SAL_QUOTE_STATUS_ID = '1' THEN 1 ELSE 0 END) AS [Created],
    SUM(CASE WHEN sq.SAL_QUOTE_STATUS_ID = '4' THEN 1 ELSE 0 END) AS [Ordered],
    SUM(CASE WHEN sq.SAL_QUOTE_STATUS_ID = '3' THEN 1 ELSE 0 END) AS [Rejected],
    SUM(sq.AMOUNT_INCLUDING_TAX) AS [Quote Amount],
    SUM(sq.COST) AS [Quote Cost],
    -- 子查询：计算该销售所有已下单报价对应的总销售额
    (SELECT SUM(o.TOTAL_SALES_AMOUNT)
     FROM SAL_ORDERS o
     JOIN SAL_QUOTES sq_sub ON o.SAL_QUOTE_ID = sq_sub.SAL_QUOTE_ID
     WHERE sq_sub.SALESPERSON_ID = sp.SALESPERSON_ID) AS [Total Sales]
FROM SALESPERSONS sp
JOIN SAL_QUOTES sq ON sp.SALESPERSON_ID = sq.SALESPERSON_ID
GROUP BY sp.FIRST_NAME, sp.LAST_NAME, sp.SALESPERSON_ID;

方案2：左连接预聚合的子查询（性能更优） #

如果你的数据量较大，上面的关联子查询可能会重复计算，推荐先预聚合订单数据再关联，效率更高：

SELECT 
    sp.FIRST_NAME + ' ' + sp.LAST_NAME AS [Salesperson],
    SUM(CASE WHEN sq.SAL_QUOTE_STATUS_ID = '1' THEN 1 ELSE 0 END) AS [Created],
    SUM(CASE WHEN sq.SAL_QUOTE_STATUS_ID = '4' THEN 1 ELSE 0 END) AS [Ordered],
    SUM(CASE WHEN sq.SAL_QUOTE_STATUS_ID = '3' THEN 1 ELSE 0 END) AS [Rejected],
    SUM(sq.AMOUNT_INCLUDING_TAX) AS [Quote Amount],
    SUM(sq.COST) AS [Quote Cost],
    -- 用COALESCE确保无订单的销售显示0而非NULL
    COALESCE(sales_agg.TotalSales, 0) AS [Total Sales]
FROM SALESPERSONS sp
JOIN SAL_QUOTES sq ON sp.SALESPERSON_ID = sq.SALESPERSON_ID
-- 左连接预聚合的订单销售额
LEFT JOIN (
    SELECT 
        sq_sub.SALESPERSON_ID,
        SUM(o.TOTAL_SALES_AMOUNT) AS TotalSales
    FROM SAL_ORDERS o
    JOIN SAL_QUOTES sq_sub ON o.SAL_QUOTE_ID = sq_sub.SAL_QUOTE_ID
    GROUP BY sq_sub.SALESPERSON_ID
) sales_agg ON sp.SALESPERSON_ID = sales_agg.SALESPERSON_ID
GROUP BY sp.FIRST_NAME, sp.LAST_NAME, sp.SALESPERSON_ID, sales_agg.TotalSales;

额外说明 #

• 我假设了一些表名和字段名（比如SALESPERSONS、SAL_ORDERS、TOTAL_SALES_AMOUNT），你需要根据自己的数据库实际结构替换这些名称。
• 如果你的“总销售额”就是已下单报价的金额（不需要关联订单表），那可以直接用聚合函数实现，不需要子查询：

  SUM(CASE WHEN sq.SAL_QUOTE_STATUS_ID = '4' THEN sq.AMOUNT_INCLUDING_TAX ELSE 0 END) AS [Total Sales]
  

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