Access查询求和翻倍/三倍异常：关联聚合数据错误求助

问题说明 #

关联Shelf表与[Shelf Persons]、[Shelf Meals]两个视图的查询执行后，出现求和结果翻倍/三倍的异常，已定位问题为先JOIN后聚合，但修改SQL时频繁出现语法错误或空值，需要易懂的修正方案。

现有数据结构 #

TABLE Shelf #

存储日期、家庭数、男性、女性、老人、儿童数量等数据，示例数据：

VIEW [Shelf Persons] #

按日期、性别统计人数的视图，SQL代码：

SELECT
    Q.[Date],
    Q.Gender,
    Sum(Q.N) AS CountOfGender
FROM
    (
        SELECT
            [Date],
            "F" AS Gender,
            Female AS N
        FROM
            Shelf
        WHERE
            Female > 0
        UNION ALL
        SELECT
            [Date],
            "M" AS Gender,
            Male AS N
        FROM
            Shelf
        WHERE
            Male > 0
    ) AS Q
GROUP BY
    Q.[Date],
    Q.Gender
HAVING
    (
        (Q.[Date] BETWEEN [Forms]![Report Parameters]![Start Date] And [Forms]![Report Parameters]![End Date])
    );

VIEW [Shelf Meals] #

按日期统计总人数及餐食数的视图，SQL代码：

SELECT
    [Shelf Persons].Date,
    SUM([Shelf Persons].CountOfGender) AS SumOfCountOfGender, 
    SUM([CountOfGender] * 9) AS Meals
FROM
    [Shelf Persons]
GROUP BY
    [Shelf Persons].Date;

原关联查询SQL代码 #

SELECT
    Shelf.Date, 
    COUNT(Shelf.Date) AS CountOfDate, 
    SUM(Shelf.Family) AS SumOfFamily, 
    SUM([Shelf Persons].CountOfGender) AS SumOfCountOfGender, 
    SUM([Shelf Meals].Meals) AS SumOfMeals, 
    SUM(Shelf.Elderly) AS SumOfElderly, 
    SUM(Shelf.Children) AS SumOfChildren
FROM
    (
        Shelf 
        LEFT JOIN [Shelf Persons] ON Shelf.Date = [Shelf Persons].Date
    ) 
    LEFT JOIN [Shelf Meals] ON Shelf.Date = [Shelf Meals].Date
GROUP BY
    Shelf.Date
HAVING
    (
        (Shelf.Date BETWEEN [Forms]![Report Parameters]![Start Date] AND [Forms]![Report Parameters]![End Date])
    );

当前错误结果 #

期望正确结果 #

修正方案 #

核心思路 #

先对Shelf表按日期完成聚合计算（得到每日的统计值），再将这个聚合结果与已经按日期聚合好的[Shelf Persons]、[Shelf Meals]视图关联，从根源避免JOIN时产生笛卡尔积导致重复统计。

修正后的SQL代码 #

SELECT
    ShelfAgg.Date,
    ShelfAgg.CountOfDate,
    ShelfAgg.SumOfFamily,
    Persons.SumOfCountOfGender,
    Meals.Meals,
    ShelfAgg.SumOfElderly,
    ShelfAgg.SumOfChildren
FROM
    (
        -- 先对Shelf表按日期聚合，计算需要的统计值
        SELECT
            Date,
            COUNT(Date) AS CountOfDate,
            SUM(Family) AS SumOfFamily,
            SUM(Elderly) AS SumOfElderly,
            SUM(Children) AS SumOfChildren
        FROM Shelf
        WHERE Date BETWEEN [Forms]![Report Parameters]![Start Date] AND [Forms]![Report Parameters]![End Date]
        GROUP BY Date
    ) AS ShelfAgg
    LEFT JOIN 
    (
        -- 对[Shelf Persons]按日期再次聚合，得到每日总人数
        SELECT
            Date,
            SUM(CountOfGender) AS SumOfCountOfGender
        FROM [Shelf Persons]
        GROUP BY Date
    ) AS Persons ON ShelfAgg.Date = Persons.Date
    LEFT JOIN [Shelf Meals] AS Meals ON ShelfAgg.Date = Meals.Date;

代码说明 #

1. ShelfAgg子查询：先对Shelf表按日期聚合，直接计算出每日的记录数、家庭数、老人数、儿童数，完成原查询中对Shelf表的聚合需求，避免后续JOIN时重复统计。
2. Persons子查询：原[Shelf Persons]视图按日期+性别分组，这里再次按日期聚合，得到每日总人数，和原视图统计逻辑一致。
3. 关联逻辑：聚合后的Shelf结果分别与Persons、Meals视图按日期关联，每个日期仅1条记录，不会产生重复匹配，求和结果不再异常。
4. 日期筛选：将原HAVING中的日期筛选移到ShelfAgg的WHERE中，提前过滤数据，提升查询效率。

简洁替代方案（小数据量适用） #

如果不想修改原视图，可使用Access的DSum函数直接在Shelf聚合查询中调用统计值，写法更简洁：

SELECT
    Date,
    COUNT(Date) AS CountOfDate,
    SUM(Family) AS SumOfFamily,
    DSum("CountOfGender", "[Shelf Persons]", "Date=#" & Format(Date, "yyyy-mm-dd") & "#") AS SumOfCountOfGender,
    DSum("Meals", "[Shelf Meals]", "Date=#" & Format(Date, "yyyy-mm-dd") & "#") AS SumOfMeals,
    SUM(Elderly) AS SumOfElderly,
    SUM(Children) AS SumOfChildren
FROM Shelf
WHERE Date BETWEEN [Forms]![Report Parameters]![Start Date] AND [Forms]![Report Parameters]![End Date]
GROUP BY Date;

内容的提问来源于stack exchange，提问作者C-Note