Access 2000多表聚合查询Race Wins计数异常求助

以下单表聚合查询运行正常：

SELECT
    SessionResults.Driver,
    SUM( IIF( SessionResults.Place = 1, 1, NULL ) ) AS [Race Wins], 
    ROUND( AVG( SessionResults.Place ), 2 )         AS [Avg Race Pos], 
    SUM( SessionResults.TotalLaps )                 AS [Total Laps]
FROM
    SessionResults
GROUP BY
    SessionResults.Driver;

但以下多表关联查询存在问题：

SELECT DISTINCT
    SessionResults.Driver,
    ( SUM( IIF( [SessionResults.Place] = 1, 1, NULL ) ) ) AS [Race Wins],
    ROUND( AVG( SessionResults.Place ), 2 )               AS [Avg Race Placing], 
    ROUND( AVG( SegmentData.SegPlace ), 2 )               AS [Avg Heat Placing], 
    COUNT( SessionResults.TotalLaps )                     AS [Total Laps], 
    EventHistory.TrackID                                  AS [Track/Layout],
    MAX( EventHistory.Date )                              AS [Last Race]
FROM
    SessionResults,
    SegmentData,
    EventHistory
WHERE
    (
        (
            ( SessionResults.EventID ) = EventHistory.EventID
            AND
            SegmentData.EventID = SessionResults.EventID
        )
    )
GROUP BY
    SessionResults.Driver,
    EventHistory.TrackID;

该多表查询中[Race Wins]列计数虚高（单表查询时最大值为36，多表后超600），但其他列数据准确，使用Access 2000（V9），尝试多种连接方式未解决。

• 笛卡尔积导致重复统计：SessionResults与SegmentData是一对多关联（一场赛事结果对应多条分段数据），直接关联后，每条SessionResults记录会被重复匹配多次（匹配次数等于对应SegmentData的记录数）。聚合计算SUM(IIF(SessionResults.Place=1,1,NULL))时，原本1次的冠军记录会被重复统计多次，最终结果被放大。
• SELECT DISTINCT无效：DISTINCT是对最终结果行去重，但聚合计算在去重前已完成，重复记录早已被多次计入SUM。

核心思路 #

先对单表做独立聚合，再关联其他表，从根源避免连接带来的重复统计：

1. 单独聚合SessionResults，得到每个车手的赛事级统计数据（Race Wins、Avg Race Placing、Total Laps）；
2. 单独聚合SegmentData，得到每个车手的分段平均排名（Avg Heat Placing）；
3. 将上述两个聚合结果与EventHistory关联，按Driver和TrackID分组整合最终数据。

适配Access 2000的修正SQL #

SELECT
    sr_agg.Driver,
    sr_agg.[Race Wins],
    sr_agg.[Avg Race Placing],
    sd_agg.[Avg Heat Placing],
    sr_agg.[Total Laps],
    eh.TrackID AS [Track/Layout],
    MAX(eh.Date) AS [Last Race]
FROM
    (
        -- 预聚合SessionResults的赛事统计，确保每条冠军记录只统计一次
        SELECT
            Driver,
            SUM(IIF(Place = 1, 1, NULL)) AS [Race Wins],
            ROUND(AVG(Place), 2) AS [Avg Race Placing],
            SUM(TotalLaps) AS [Total Laps],
            EventID
        FROM SessionResults
        GROUP BY Driver, EventID
    ) AS sr_agg
    INNER JOIN EventHistory AS eh ON sr_agg.EventID = eh.EventID
    LEFT JOIN (
        -- 预聚合SegmentData的分段排名
        SELECT
            Driver,
            EventID,
            ROUND(AVG(SegPlace), 2) AS [Avg Heat Placing]
        FROM SegmentData
        GROUP BY Driver, EventID
    ) AS sd_agg ON sr_agg.EventID = sd_agg.EventID AND sr_agg.Driver = sd_agg.Driver
GROUP BY
    sr_agg.Driver,
    sr_agg.[Race Wins],
    sr_agg.[Avg Race Placing],
    sd_agg.[Avg Heat Placing],
    sr_agg.[Total Laps],
    eh.TrackID;

补充说明 #

• 子查询预聚合SessionResults，从根源避免了后续连接导致的重复计数；
• 对SegmentData做预聚合，保证分段平均排名的准确性；
• 使用INNER JOIN/LEFT JOIN明确连接关系，替代原查询的隐式连接，逻辑更清晰，也能避免不必要的笛卡尔积。

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