mysql - 当子查询引用 FROM 子句中使用的外表时，未知列

Question

我有这张表:

person table
| id | name    |
|  1 | person1 |
|  2 | person2 |

person_grade table

| id | person_id | grade  | grade_date |
|  1 |     1     | grade1 | 2010-01-01 |
|  2 |     1     | grade2 | 2012-01-01 |
|  3 |     2     | grade3 | 2010-05-05 |
|  4 |     2     | grade4 | 2012-03-03 |

我想知道特定时间的人员成绩，例如 2012-02-02。如何实现这一目标？

我得到的最接近的是，通过这个查询:

SELECT t1.id, t1.name, 
(SELECT grade FROM (
(SELECT s1.grade, s1.grade_date FROM person_grade AS s1 
WHERE s1.grade_date >= '2012-02-01'  AND s1.person_id = t1.id
ORDER BY s1.grade_date LIMIT 1)
UNION ALL
(SELECT s1.grade, s1.grade_date FROM person_grade AS s1 
WHERE s1.grade_date <= '2012-02-01'  AND s1.person_id = t1.id
ORDER BY s1.grade_date DESC LIMIT 1)
) AS ss ORDER BY grade_date LIMIT 1) AS grade_person
FROM person AS t1

但是在 MySQL 上给了我一个错误

"Unknown column 't1.id' in 'where clause'".

请指教。
TIA

Answer 1

SELECT name,grade FROM person p
INNER JOIN person_grade pg
ON p.id=pg.person_id
WHERE pg.grade_date='2012-02-02'