如何合并同结构表AgencyA与AgencyB并执行聚合统计查询？

你可以通过子查询或者**CTE（公共表表达式）**两种方式，先合并AgencyA和AgencyB的数据，再对合并后的结果执行聚合统计，具体写法如下：

方法1：使用子查询 #

将两表合并的语句作为子查询，嵌套在聚合查询的FROM子句中：

SELECT 
    Subject,
    SUM(License_Fee) AS Gross,
    COUNT(DISTINCT Event_Combined) AS Total_Sales,
    SUM(License_Fee)/COUNT(DISTINCT Event_Combined) AS Result
FROM (
    SELECT Subject, Event_Combined, License_Fee
    FROM AgencyA
    UNION ALL
    SELECT Subject, Event_Combined, License_Fee
    FROM AgencyB
) AS CombinedAgencies
GROUP BY Subject
ORDER BY Gross DESC

方法2：使用CTE（公共表表达式） #

先通过WITH定义合并后的数据集，再基于这个数据集做聚合，可读性更强：

WITH CombinedAgencies AS (
    SELECT Subject, Event_Combined, License_Fee
    FROM AgencyA
    UNION ALL
    SELECT Subject, Event_Combined, License_Fee
    FROM AgencyB
)
SELECT 
    Subject,
    SUM(License_Fee) AS Gross,
    COUNT(DISTINCT Event_Combined) AS Total_Sales,
    SUM(License_Fee)/COUNT(DISTINCT Event_Combined) AS Result
FROM CombinedAgencies
GROUP BY Subject
ORDER BY Gross DESC

说明 #

两种写法的逻辑完全一致：

• 先用UNION ALL合并两张表的全部数据（保留重复行，符合原始合并需求）
• 再按Subject分组，计算每组的总许可费用、去重后的销售事件数，以及两者的比值
• 最后按总费用倒序排序

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