如何在ORDER BY中使用不同聚合函数生成的两列实现销售数据排序

你可以通过窗口聚合函数计算每个销售人员的总营收，将其作为第一排序条件，再按单客户营收降序排序即可实现需求。

方案1：窗口函数实现（兼容MySQL8.0+、PostgreSQL、SQL Server、Oracle等所有支持窗口函数的主流数据库） #

如果不需要在结果中展示销售人员总营收，可直接将计算逻辑写在ORDER BY子句中：

SELECT 
    ORD.SALESPERSONPERSONID,  
    ORD.CUSTOMERID,
    SUM(LINE.QUANTITY * LINE.UNITPRICE) AS SALESPERSON_CUSTOMER_REVENUE
FROM SALES_ORDERS ORD
INNER JOIN SALES_ORDERLINES LINE
    ON ORD.ORDERID = LINE.ORDERID
GROUP BY ORD.SALESPERSONPERSONID, ORD.CUSTOMERID
ORDER BY 
    -- 第一排序：按销售人员总营收降序
    SUM(SUM(LINE.QUANTITY * LINE.UNITPRICE)) OVER (PARTITION BY ORD.SALESPERSONPERSONID) DESC,
    -- 第二排序：同销售下按单客户营收降序
    SALESPERSON_CUSTOMER_REVENUE DESC

如果需要在结果中展示总营收，可将窗口函数写在SELECT字段中：

SELECT 
    ORD.SALESPERSONPERSONID,  
    ORD.CUSTOMERID,
    SUM(LINE.QUANTITY * LINE.UNITPRICE) AS SALESPERSON_CUSTOMER_REVENUE,
    SUM(SUM(LINE.QUANTITY * LINE.UNITPRICE)) OVER (PARTITION BY ORD.SALESPERSONPERSONID) AS SALESPERSON_TOTAL_REVENUE
FROM SALES_ORDERS ORD
INNER JOIN SALES_ORDERLINES LINE
    ON ORD.ORDERID = LINE.ORDERID
GROUP BY ORD.SALESPERSONPERSONID, ORD.CUSTOMERID
ORDER BY SALESPERSON_TOTAL_REVENUE DESC, SALESPERSON_CUSTOMER_REVENUE DESC

方案2：子查询关联实现（兼容不支持窗口函数的低版本数据库） #

SELECT 
    t1.SALESPERSONPERSONID,
    t1.CUSTOMERID,
    t1.SALESPERSON_CUSTOMER_REVENUE
FROM (
    -- 计算每个销售对应单客户的营收
    SELECT 
        ORD.SALESPERSONPERSONID,  
        ORD.CUSTOMERID,
        SUM(LINE.QUANTITY * LINE.UNITPRICE) AS SALESPERSON_CUSTOMER_REVENUE
    FROM SALES_ORDERS ORD
    INNER JOIN SALES_ORDERLINES LINE
        ON ORD.ORDERID = LINE.ORDERID
    GROUP BY ORD.SALESPERSONPERSONID, ORD.CUSTOMERID
) t1
INNER JOIN (
    -- 计算每个销售的总营收
    SELECT 
        ORD.SALESPERSONPERSONID,
        SUM(LINE.QUANTITY * LINE.UNITPRICE) AS TOTAL_REVENUE
    FROM SALES_ORDERS ORD
    INNER JOIN SALES_ORDERLINES LINE
        ON ORD.ORDERID = LINE.ORDERID
    GROUP BY ORD.SALESPERSONPERSONID
) t2 ON t1.SALESPERSONPERSONID = t2.SALESPERSONPERSONID
ORDER BY t2.TOTAL_REVENUE DESC, t1.SALESPERSON_CUSTOMER_REVENUE DESC

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