VBA查询Excel按年汇总商品价格，缺失年份返回0报错如何解决？

你遇到的问题核心有两个：

1. 之前用IIF/COALESCE处理SUM结果的思路错误：没有交易记录的年份根本不会出现在GROUP BY的分组结果里，不是SUM值为NULL，是对应的行不存在，所以仅处理SUM值无法补全缺失的年份行。
2. 报错的直接原因：
   • 嵌套聚合错误：第二个测试SQL里写了SUM(IIF(data.[price] IS NULL,0,SUM(data.[price])))，GROUP BY查询中不允许聚合函数嵌套使用，所以报参数类型/范围错误。
   • 函数不兼容：ACE OLEDB（Access数据库引擎）不支持COALESCE函数，调用不存在的函数会直接导致查询失败，报Recordset Open错误。

核心逻辑是先生成「所有商品 + 所有目标年份」的全量组合，再左关联原有的交易汇总结果，没有交易的年份汇总值补0即可，分两种场景实现：

场景1：年份范围固定 #

如果已经确定要统计的年份区间为2010-2021，直接用UNION ALL拼接年份列表即可，SQL写法如下：

SELECT 
    t.article,
    t.y AS 年份,
    IIF(s.total_price IS NULL, 0, s.total_price) AS 年度汇总价格
FROM 
    (
        -- 子查询1：生成所有商品+所有年份的全量组合
        SELECT a.article, b.y 
        FROM 
            (SELECT DISTINCT article FROM [data$]) a,
            (
                SELECT 2010 AS y UNION ALL SELECT 2011 UNION ALL SELECT 2012 UNION ALL
                SELECT 2013 UNION ALL SELECT 2014 UNION ALL SELECT 2015 UNION ALL
                SELECT 2016 UNION ALL SELECT 2017 UNION ALL SELECT 2018 UNION ALL
                SELECT 2019 UNION ALL SELECT 2020 UNION ALL SELECT 2021
            ) b
    ) t
LEFT JOIN
    (
        -- 子查询2：原有的交易汇总逻辑
        SELECT 
            article, 
            YEAR([date]) AS y, 
            SUM(price) AS total_price 
        FROM [data$] 
        GROUP BY article, YEAR([date])
    ) s
ON t.article = s.article AND t.y = s.y

注：用YEAR()函数取年份比FORMAT()性能更好，返回数值型年份也更适合关联匹配

场景2：年份范围动态 #

如果需要自动匹配数据中的最小和最大年份，可以先查年份边界再动态拼接SQL，调整后的VB代码如下：

Dim sql As String
Dim rs As New ADOR.Recordset
Dim arr As Variant
Const filepath As String = "C:\Users\XXXXXX\Documents\excel\XXXXXX.xlsm"
    
Dim connectionString As String
connectionString = _
    "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=""" & filepath & """;" & _
    "Extended Properties=""Excel 12.0;HDR=Yes"""

' 第一步：查询数据中的最小、最大年份
Dim minYear As Integer, maxYear As Integer
Dim yearSql As String, yearUnion As String
yearSql = "SELECT MIN(YEAR([date])), MAX(YEAR([date])) FROM [data$]"
rs.Open yearSql, connectionString
minYear = rs.Fields(0).Value
maxYear = rs.Fields(1).Value
rs.Close

' 第二步：动态拼接年份的UNION ALL部分
yearUnion = ""
For y = minYear To maxYear
    If yearUnion <> "" Then yearUnion = yearUnion & " UNION ALL "
    yearUnion = yearUnion & "SELECT " & y & " AS y"
Next

' 第三步：拼接最终查询SQL
sql = "SELECT t.article, t.y AS 年份, IIF(s.total_price IS NULL, 0, s.total_price) AS 年度汇总价格 " & _
      "FROM (SELECT a.article, b.y FROM (SELECT DISTINCT article FROM [data$]) a, (" & yearUnion & ") b) t " & _
      "LEFT JOIN (SELECT article, YEAR([date]) AS y, SUM(price) AS total_price FROM [data$] GROUP BY article, YEAR([date])) s " & _
      "ON t.article = s.article AND t.y = s.y"

' 执行查询获取结果
rs.Open sql, connectionString
arr = rs.GetRows
rs.Close

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