在MS Access中实现查询结果逐行执行子查询的需求

Instead of running two separate queries and looping through results (like you did in PHP), you can merge these into a single Access query by joining your rider results with an aggregated subquery that calculates the number of riders per race and class. Here's how to do it:

Option 1: Single Query with Subquery #

This version includes the count calculation directly as a subquery within the main query:

SELECT 
    schedule2.Date, 
    riders.ridername, 
    results.finish, 
    results.points, 
    classes.class, 
    seasons.season,
    ClassRaceCounts.RiderCount
FROM 
    (schedule2 RIGHT JOIN 
        (classes RIGHT JOIN 
            ((riders RIGHT JOIN results ON riders.id = results.rider_id) 
            LEFT JOIN seasons ON results.season_id = seasons.id) 
        ON classes.id = results.class_id) 
    ON schedule2.id = results.schedule_id)
INNER JOIN 
    (SELECT 
        results.schedule_id, 
        results.class_id, 
        COUNT(DISTINCT results.rider_id) AS RiderCount
     FROM 
         results
     INNER JOIN schedule2 ON results.schedule_id = schedule2.id
     INNER JOIN seasons ON schedule2.season_id = seasons.id
     GROUP BY results.schedule_id, results.class_id) AS ClassRaceCounts
ON 
    schedule2.id = ClassRaceCounts.schedule_id 
    AND results.class_id = ClassRaceCounts.class_id
WHERE 
    riders.id = [Enter Rider ID:]  -- Access uses bracket parameters for prompts
GROUP BY 
    schedule2.Date, 
    riders.ridername, 
    results.finish, 
    results.points, 
    classes.class, 
    seasons.season, 
    schedule2.id, 
    results.class_id, 
    riders.id,
    ClassRaceCounts.RiderCount
ORDER BY 
    schedule2.Date;

Option 2: Use a Saved Summary Query (Cleaner for Reuse) #

If you want to reuse the class count logic elsewhere, save this as a separate query first (name it qryClassRaceCounts):

SELECT 
    results.schedule_id, 
    results.class_id, 
    COUNT(DISTINCT results.rider_id) AS RiderCount
FROM 
    results
INNER JOIN schedule2 ON results.schedule_id = schedule2.id
INNER JOIN seasons ON schedule2.season_id = seasons.id
GROUP BY results.schedule_id, results.class_id;

Then reference it in your main rider results query:

SELECT 
    schedule2.Date, 
    riders.ridername, 
    results.finish, 
    results.points, 
    classes.class, 
    seasons.season,
    qryClassRaceCounts.RiderCount
FROM 
    (schedule2 RIGHT JOIN 
        (classes RIGHT JOIN 
            ((riders RIGHT JOIN results ON riders.id = results.rider_id) 
            LEFT JOIN seasons ON results.season_id = seasons.id) 
        ON classes.id = results.class_id) 
    ON schedule2.id = results.schedule_id)
INNER JOIN qryClassRaceCounts ON 
    schedule2.id = qryClassRaceCounts.schedule_id 
    AND results.class_id = qryClassRaceCounts.class_id
WHERE 
    riders.id = [Enter Rider ID:]
GROUP BY 
    schedule2.Date, 
    riders.ridername, 
    results.finish, 
    results.points, 
    classes.class, 
    seasons.season, 
    schedule2.id, 
    results.class_id, 
    riders.id,
    qryClassRaceCounts.RiderCount
ORDER BY 
    schedule2.Date;

Key Changes Explained: #

1. Replaced HAVING with WHERE: Filtering by riders.id is a non-aggregated condition, so using WHERE is more efficient than HAVING in Access.
2. Added the Class Count Join: The subquery/saved query calculates the number of unique riders per race (schedule_id) and class (class_id), then joins back to the main results using those two fields as keys.
3. Access Parameter Prompt: [Enter Rider ID:] will prompt the user for a rider ID when running the query. If you're calling this from code (like PHP via ODBC), you can replace this with parameter placeholders appropriate for your driver (e.g., ? for PDO).

This query will return exactly the same result set as your PHP code: each row shows a rider's race details alongside the total number of riders in that race's class.

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