SSMS中合并三个引用同一张表的SELECT语句问题求助

先说说你报错的原因 #

你之前的代码里，把未做任何聚合的主表[dbo].[fmdr_1.19]和三个聚合后的子表做JOIN，然后只GROUP BY N0.[Account Name]——但T1.Q1、T2.Q2这些列既不在聚合函数里，也没出现在GROUP BY子句中，这就违反了SQL的分组规则。而且其实完全没必要引入主表N0，三个子表已经按Account Name做了聚合，直接在它们之间做连接就可以。

方案一：修正JOIN思路（适合理解表连接逻辑） #

如果想继续用表连接的方式，建议用全外连接（避免某些账户只有某季度数据时被过滤掉），同时用COALESCE统一取账户名称：

SELECT 
    COALESCE(T1.[Account Name], T2.[Account Name], T3.[Account Name]) AS [Account Name],
    T1.Q1,
    T2.Q2,
    T3.Q3
FROM 
    (SELECT [Account Name], SUM(amount) AS Q1 FROM [dbo].[fmdr_1.19] WHERE DATEPART(month, Period)=3 GROUP BY [Account Name]) T1
FULL OUTER JOIN 
    (SELECT [Account Name], SUM(amount) AS Q2 FROM [dbo].[fmdr_1.19] WHERE DATEPART(month, Period)=6 GROUP BY [Account Name]) T2 
    ON T1.[Account Name] = T2.[Account Name]
FULL OUTER JOIN 
    (SELECT [Account Name], SUM(amount) AS Q3 FROM [dbo].[fmdr_1.19] WHERE DATEPART(month, Period)=9 GROUP BY [Account Name]) T3 
    ON COALESCE(T1.[Account Name], T2.[Account Name]) = T3.[Account Name]

这里用FULL OUTER JOIN是为了确保所有有数据的账户都能被展示，哪怕它只在某一个季度有记录。

方案二：条件聚合（推荐，性能更优） #

这种方式只需要扫描一次原表，比三次扫描表的JOIN方案效率高很多，尤其是当表数据量较大时：

SELECT 
    [Account Name],
    SUM(CASE WHEN DATEPART(month, Period) = 3 THEN amount ELSE 0 END) AS Q1,
    SUM(CASE WHEN DATEPART(month, Period) = 6 THEN amount ELSE 0 END) AS Q2,
    SUM(CASE WHEN DATEPART(month, Period) = 9 THEN amount ELSE 0 END) AS Q3
FROM 
    [dbo].[fmdr_1.19]
WHERE 
    DATEPART(month, Period) IN (3,6,9) -- 过滤掉无关月份，进一步提升效率
GROUP BY 
    [Account Name]

核心逻辑是用CASE WHEN对不同月份的amount做条件判断，只对符合季度条件的金额求和，最终直接得到按账户分组的季度汇总结果。

额外优化：按季度维度统计（如果你的季度是标准定义） #

如果你的季度是常规的1-3月Q1、4-6月Q2、7-9月Q3，可以直接用DATEPART(quarter, Period)来简化条件，避免手动指定月份可能出错：

SELECT 
    [Account Name],
    SUM(CASE WHEN DATEPART(quarter, Period) = 1 THEN amount ELSE 0 END) AS Q1,
    SUM(CASE WHEN DATEPART(quarter, Period) = 2 THEN amount ELSE 0 END) AS Q2,
    SUM(CASE WHEN DATEPART(quarter, Period) = 3 THEN amount ELSE 0 END) AS Q3
FROM 
    [dbo].[fmdr_1.19]
WHERE 
    DATEPART(quarter, Period) IN (1,2,3)
GROUP BY 
    [Account Name]

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