根据日期计算年龄，并根据种族结果进行匹配以进行排序

Question

I'm trying to add a new page onto an old site, a records page that'd show which players won the most money or won the race when they were a certain age

The user table looks like this

***********************************
| id | name   | age  | bday       |
| 1  | bob    | 15   | 2000-07-30 |
| 2  | john   | 14   | 2001-07-30 |
| 3  | mary   | 13   | 2002-07-30 |
***********************************

the race_results table looks like this

************************************************************
| id | raceid | userid | place | winnings | date           |
| 1  | 1      | 1      | 1     | 1000     | 2006-04-10     |
| 2  | 1      | 2      | 5     | 50       | 2005-02-15     |
| 3  | 1      | 3      | 6     | 50       | 2010-06-12     |
| 4  | 2      | 1      | 1     | 1000     | 2009-05-29     |
| 5  | 2      | 2      | 3     | 250      | 2003-01-12     |
************************************************************

What's the most practical approach to a query that'd calculate the year range when Bob was 3 years old and match that with the race results table to see how many times he won 1st place within that particular date range?

Answer 1

SELECT COUNT(*)
    FROM 'race_results'
    INNER JOIN 'user' on user.id = race_results.userid
    WHERE user.name = 'bob'
        AND race_results.place = 1
        AND race_results.date >= ADDDATE(user.bday, INTERVAL 3 YEAR)
        AND race_results.date <  ADDDATE(user.bday, INTERVAL 4 YEAR);

To get a list of all 3 yr old 1st placers and how many times they won, not just for 'bob' ...

SELECT user.name, COUNT(*)
    FROM 'race_results'
    INNER JOIN 'user' on user.id = race_results.userid
    WHERE race_results.place = 1
        AND race_results.date >= ADDDATE(user.bday, INTERVAL 3 YEAR)
        AND race_results.date <  ADDDATE(user.bday, INTERVAL 4 YEAR)
    GROUP BY user.name;