Access查询：合并重复食材行并汇总数量的实现方案

假设你的数据库包含两个核心表：Recipes（食谱表，含RecipeID、RecipeName、Status字段）和Ingredients（食材表，含IngredientID、RecipeID、IngredientName、Unit、Quantity字段）。

你之前报错的核心原因是：SELECT语句里所有未用聚合函数的字段，必须全部放到GROUP BY子句中。正确的SQL写法如下，能直接合并重复食材并汇总数量：

SELECT 
    Ing.IngredientName, 
    Ing.Unit, 
    SUM(Ing.Quantity) AS TotalQuantity
FROM 
    Recipes AS Rec
INNER JOIN 
    Ingredients AS Ing ON Rec.RecipeID = Ing.RecipeID
WHERE 
    Rec.Status = 'Planned'
GROUP BY 
    Ing.IngredientName, Ing.Unit;

注意事项： #

• 如果你不需要显示食谱名称这类非食材核心字段，就不要把它们加到SELECT里，避免触发报错。
• 如果需要同时显示该食材对应的所有食谱名称，Access原生不支持类似MySQL的GROUP_CONCAT功能，得用VBA自定义函数实现。

1. 自定义聚合函数（查询中合并食谱名称） #

打开Access的VBA编辑器（按Alt+F11），插入一个模块，粘贴以下代码：

Function ConcatRecipes(IngredientName As String) As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sqlStr As String
    Dim recipeList As String
    
    Set db = CurrentDb()
    ' 拼接SQL，处理单引号转义避免报错
    sqlStr = "SELECT DISTINCT Rec.RecipeName " & _
             "FROM Recipes AS Rec INNER JOIN Ingredients AS Ing ON Rec.RecipeID = Ing.RecipeID " & _
             "WHERE Rec.Status = 'Planned' AND Ing.IngredientName = '" & Replace(IngredientName, "'", "''") & "'"
    
    Set rs = db.OpenRecordset(sqlStr)
    Do While Not rs.EOF
        If recipeList <> "" Then recipeList = recipeList & ", "
        recipeList = recipeList & rs!RecipeName
        rs.MoveNext
    Loop
    
    ConcatRecipes = recipeList
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Function

之后在查询中调用这个函数，就能同时看到食材对应所有食谱：

SELECT 
    Ing.IngredientName, 
    Ing.Unit, 
    SUM(Ing.Quantity) AS TotalQuantity,
    ConcatRecipes(Ing.IngredientName) AS UsedInRecipes
FROM 
    Recipes AS Rec
INNER JOIN 
    Ingredients AS Ing ON Rec.RecipeID = Ing.RecipeID
WHERE 
    Rec.Status = 'Planned'
GROUP BY 
    Ing.IngredientName, Ing.Unit;

2. VBA生成独立的食材清单文件 #

如果需要直接导出汇总好的清单到文本文件，用这段代码：

Sub GenerateCampingIngredientList()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim outputStr As String
    Dim savePath As String
    
    Set db = CurrentDb()
    ' 执行汇总查询
    Set rs = db.OpenRecordset("SELECT Ing.IngredientName, Ing.Unit, SUM(Ing.Quantity) AS TotalQuantity " & _
                              "FROM Recipes AS Rec INNER JOIN Ingredients AS Ing ON Rec.RecipeID = Ing.RecipeID " & _
                              "WHERE Rec.Status = 'Planned' GROUP BY Ing.IngredientName, Ing.Unit")
    
    ' 构建清单内容
    outputStr = "露营食材清单" & vbCrLf & vbCrLf
    outputStr = outputStr & "食材名称 | 单位 | 总数量" & vbCrLf
    outputStr = outputStr & "-------------------------" & vbCrLf
    
    Do While Not rs.EOF
        outputStr = outputStr & rs!IngredientName & " | " & rs!Unit & " | " & rs!TotalQuantity & vbCrLf
        rs.MoveNext
    Loop
    
    ' 保存到数据库同目录下的文本文件
    savePath = CurrentProject.Path & "\露营食材清单.txt"
    Open savePath For Output As #1
    Print #1, outputStr
    Close #1
    
    MsgBox "食材清单已生成，路径：" & savePath
    
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

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