Jet SQL查询问题：如何统计车手在不同赛道的夺冠次数

你的问题核心是多表隐式连接导致的重复计数：SessionResults和SegmentData是一对多关联（一个赛事结果对应多个分段数据），直接关联会让SessionResults的记录被重复计算，最终Wins统计失真。

修正方案：拆分聚合子查询 #

先分别对不同表的维度做聚合，再关联结果，避免重复统计。以下是修正后的Jet SQL代码：

SELECT
    sr_agg.Driver,
    sr_agg.Wins,
    sr_agg.[Avg Race Placing],
    Nz(sd_agg.[Avg Heat Placing], 0) AS [Avg Heat Placing], -- 用Nz处理无分段数据的情况
    sr_agg.[Total Laps],
    sr_agg.[Track/Layout],
    sr_agg.[Last Race]
FROM
    (
        -- 聚合赛事结果与赛道数据，计算夺冠数、平均排名等核心指标
        SELECT
            sr.Driver,
            COUNT(IIF(sr.Place = 1, 1, NULL)) AS Wins,
            ROUND(AVG(sr.Place), 2) AS [Avg Race Placing],
            COUNT(sr.TotalLaps) AS [Total Laps],
            eh.TrackID AS [Track/Layout],
            MAX(eh.Date) AS [Last Race]
        FROM SessionResults sr
        INNER JOIN EventHistory eh ON sr.EventID = eh.EventID
        GROUP BY sr.Driver, eh.TrackID
    ) AS sr_agg
LEFT JOIN
    (
        -- 聚合分段数据与赛道数据，计算平均分段排名
        SELECT
            sr.Driver,
            eh.TrackID AS [Track/Layout],
            ROUND(AVG(sd.SegPlace), 2) AS [Avg Heat Placing]
        FROM SegmentData sd
        INNER JOIN SessionResults sr ON sd.EventID = sr.EventID
        INNER JOIN EventHistory eh ON sr.EventID = eh.EventID
        GROUP BY sr.Driver, eh.TrackID
    ) AS sd_agg
ON sr_agg.Driver = sd_agg.Driver AND sr_agg.[Track/Layout] = sd_agg.[Track/Layout];

关键说明 #

1. 拆分聚合逻辑：先单独对SessionResults和EventHistory做分组聚合，确保每条赛事结果只被统计一次，这样Wins的计数会完全准确。
2. 显式连接替代隐式连接：用INNER JOIN/LEFT JOIN明确表关联关系，避免隐式连接可能产生的笛卡尔积。
3. 可选优化：统计夺冠次数也可以用SUM(IIF(sr.Place = 1, 1, 0)) AS Wins，效果一致，写法更直观。
4. Nz函数处理空值：如果某些车手在赛道没有分段数据，用Nz函数将Avg Heat Placing的空值替换为0，避免结果显示为空。

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