如何在SQL UNION查询中复用派生表/结果集别名？

咱们先搞清楚为啥你之前的写法会报错：你在UNION每个分支里定义的表别名（比如Test1、Test3）只在当前SELECT语句的作用域内有效，MySQL不会把这些别名当成整个UNION结果的全局别名，所以后面的分支根本找不到这些“表”，自然会报1146错误。

要实现复用主查询结果并追加总计行，有两种常用方案，根据你的MySQL版本选择即可：

方案一：用CTE（MySQL 8.0+推荐） #

CTE（公共表表达式）可以把主查询的结果临时存储成一个可复用的数据集，后续的总计查询直接引用这个CTE就行，代码可读性和维护性都很高。

WITH PrimaryQuery AS (
    -- 这里放你的实际主查询，注意给第一列加别名保证列匹配
    SELECT 
        COALESCE(referrers.name,order_items.ReferrerID) AS Marketplace,
        SUM(order_items.quantity) as QtySold, 
        ROUND(SUM((order_items.quantity*order_items.price+order_items.shippingcosts)/((100+order_items.vat)/100)), 2) as TotalRevenueNetto, 
        ROUND(100*SUM(order_items.quantity*order_items.purchasepricenet)/SUM((order_items.quantity*order_items.price+order_items.shippingcosts)/((100+order_items.vat)/100)), 1) as PurchasePrice, 
        ROUND(100*SUM(order_items.quantity*COALESCE(order_items.calculatedfee,0)+order_items.quantity*COALESCE(order_items.calculatedcost,0))/SUM((order_items.quantity*order_items.price+order_items.shippingcosts)/((100+order_items.vat)/100)), 1) as Costs, 
        ROUND(100*SUM(order_items.calculatedprofit) / SUM( (order_items.quantity*order_items.price + order_items.shippingcosts)/((100+order_items.vat)/100) ) , 1) as Profit, 
        COALESCE(round(100*Returns.TotalReturns_Qty/SUM(order_items.quantity),2),0) as TotalReturns 
    FROM order_items 
    LEFT JOIN (
        SELECT order_items.ReferrerID as ReferrerID, sum(order_items.quantity) as TotalReturns_Qty 
        FROM order_items 
        WHERE OrderType='returns' and OrderTimeStamp>='2017-12-1 00:00:00' 
        GROUP BY order_items.ReferrerID
    ) as Returns ON Returns.ReferrerID = order_items.ReferrerID 
    LEFT JOIN `referrers` on `referrers`.`referrerId` = `order_items`.`ReferrerID` 
    WHERE ( 
        ( order_items.BundleItemID in ('-1', '0') and order_items.OrderType in ('order', '') ) 
        or ( order_items.BundleItemID is NULL and order_items.OrderType = 'returns' ) 
    ) and order_items.OrderTimestamp >= '2017-12-1 00:00:00' 
    GROUP BY order_items.ReferrerID 
)
-- 先取出主查询的所有数据
SELECT * FROM PrimaryQuery
UNION ALL
-- 追加总计行
SELECT 
    'All marketplaces' AS Marketplace,
    SUM(QtySold), 
    SUM(TotalRevenueNetto), 
    AVG(PurchasePrice), 
    AVG(Costs), 
    AVG(Profit), 
    AVG(TotalReturns) 
FROM PrimaryQuery
-- 统一排序，让总计行排在最后
ORDER BY CASE WHEN Marketplace = 'All marketplaces' THEN 1 ELSE 0 END, Marketplace ASC;

方案二：用派生表（兼容MySQL 5.x） #

如果你的MySQL版本低于8.0，不支持CTE，就把主查询包装成派生表，不过需要重复写一次主查询内容（或者再嵌套一层派生表）：

SELECT * FROM (
    -- 主查询作为派生表
    SELECT 
        COALESCE(referrers.name,order_items.ReferrerID) AS Marketplace,
        SUM(order_items.quantity) as QtySold, 
        ROUND(SUM((order_items.quantity*order_items.price+order_items.shippingcosts)/((100+order_items.vat)/100)), 2) as TotalRevenueNetto, 
        ROUND(100*SUM(order_items.quantity*order_items.purchasepricenet)/SUM((order_items.quantity*order_items.price+order_items.shippingcosts)/((100+order_items.vat)/100)), 1) as PurchasePrice, 
        ROUND(100*SUM(order_items.quantity*COALESCE(order_items.calculatedfee,0)+order_items.quantity*COALESCE(order_items.calculatedcost,0))/SUM((order_items.quantity*order_items.price+order_items.shippingcosts)/((100+order_items.vat)/100)), 1) as Costs, 
        ROUND(100*SUM(order_items.calculatedprofit) / SUM( (order_items.quantity*order_items.price + order_items.shippingcosts)/((100+order_items.vat)/100) ) , 1) as Profit, 
        COALESCE(round(100*Returns.TotalReturns_Qty/SUM(order_items.quantity),2),0) as TotalReturns 
    FROM order_items 
    LEFT JOIN (
        SELECT order_items.ReferrerID as ReferrerID, sum(order_items.quantity) as TotalReturns_Qty 
        FROM order_items 
        WHERE OrderType='returns' and OrderTimeStamp>='2017-12-1 00:00:00' 
        GROUP BY order_items.ReferrerID
    ) as Returns ON Returns.ReferrerID = order_items.ReferrerID 
    LEFT JOIN `referrers` on `referrers`.`referrerId` = `order_items`.`ReferrerID` 
    WHERE ( 
        ( order_items.BundleItemID in ('-1', '0') and order_items.OrderType in ('order', '') ) 
        or ( order_items.BundleItemID is NULL and order_items.OrderType = 'returns' ) 
    ) and order_items.OrderTimestamp >= '2017-12-1 00:00:00' 
    GROUP BY order_items.ReferrerID 
) AS PrimaryQuery
UNION ALL
-- 再次调用主查询派生表计算总计
SELECT 
    'All marketplaces' AS Marketplace,
    SUM(QtySold), 
    SUM(TotalRevenueNetto), 
    AVG(PurchasePrice), 
    AVG(Costs), 
    AVG(Profit), 
    AVG(TotalReturns) 
FROM (
    -- 复制主查询内容
    SELECT 
        COALESCE(referrers.name,order_items.ReferrerID) AS Marketplace,
        SUM(order_items.quantity) as QtySold, 
        ROUND(SUM((order_items.quantity*order_items.price+order_items.shippingcosts)/((100+order_items.vat)/100)), 2) as TotalRevenueNetto, 
        ROUND(100*SUM(order_items.quantity*order_items.purchasepricenet)/SUM((order_items.quantity*order_items.price+order_items.shippingcosts)/((100+order_items.vat)/100)), 1) as PurchasePrice, 
        ROUND(100*SUM(order_items.quantity*COALESCE(order_items.calculatedfee,0)+order_items.quantity*COALESCE(order_items.calculatedcost,0))/SUM((order_items.quantity*order_items.price+order_items.shippingcosts)/((100+order_items.vat)/100)), 1) as Costs, 
        ROUND(100*SUM(order_items.calculatedprofit) / SUM( (order_items.quantity*order_items.price + order_items.shippingcosts)/((100+order_items.vat)/100) ) , 1) as Profit, 
        COALESCE(round(100*Returns.TotalReturns_Qty/SUM(order_items.quantity),2),0) as TotalReturns 
    FROM order_items 
    LEFT JOIN (
        SELECT order_items.ReferrerID as ReferrerID, sum(order_items.quantity) as TotalReturns_Qty 
        FROM order_items 
        WHERE OrderType='returns' and OrderTimeStamp>='2017-12-1 00:00:00' 
        GROUP BY order_items.ReferrerID
    ) as Returns ON Returns.ReferrerID = order_items.ReferrerID 
    LEFT JOIN `referrers` on `referrers`.`referrerId` = `order_items`.`ReferrerID` 
    WHERE ( 
        ( order_items.BundleItemID in ('-1', '0') and order_items.OrderType in ('order', '') ) 
        or ( order_items.BundleItemID is NULL and order_items.OrderType = 'returns' ) 
    ) and order_items.OrderTimestamp >= '2017-12-1 00:00:00' 
    GROUP BY order_items.ReferrerID 
) AS PrimaryQuery
-- 排序控制总计行位置
ORDER BY CASE WHEN Marketplace = 'All marketplaces' THEN 1 ELSE 0 END, Marketplace ASC;

注意事项 #

1. 列匹配：主查询和总计查询的列数、数据类型必须完全一致，我给主查询的第一列加了Marketplace别名，就是为了和总计行的'All marketplaces'对应。
2. 排序逻辑：用CASE语句可以让总计行固定排在最后（或最前），避免它和其他市场名称一起排序。
3. 平均值合理性：你要确认PurchasePrice、Costs这些百分比字段的平均值是否符合业务逻辑，有时候可能需要重新计算总和再求占比，而不是直接用AVG，根据实际需求调整即可。

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