对于每个学生的最大日期对应的结果：
列的最小值或最大值不一定与所需行的其他值对齐，因此您需要做的不仅仅是计算最大日期。在版本8之前的mysql中，您可以这样做，通过计算最大日期，然后将其用作内部联接，将行限制为与最大值对应的行：

select
    temp_grade .*
from table_grade tg
inner join (
    select student, max(test_date) as test_date
    from table_grade
    group by student
    ) gd on tg.student = gd.student and tg.test_date = gd.test_date
INNER JOIN (
   SELECT ID_GRADE,GRADE,NOTE
   FROM table_grade_a 'tga'
   UNION ALL 
   SELECT ID_GRADE,TOTAL_GRADE AS GRADE,NOTE 
   FROM table_grade_a 'tgb'
   )temp_grade ON tg.ID_GRADE = temp_grade.ID_GRADE 

# WHERE tg.STUDENT = 'MIYA'

在mysql v8+中，您可以使用 row_number() over(...) 取而代之的是：

select
      temp_grade .*
from (
    select *
    , row_number() over(partition by student order by test_date DESC) as rn
    from table_grade
    )  tg
INNER JOIN (
   SELECT ID_GRADE,GRADE,NOTE
   FROM table_grade_a 'tga'
   UNION ALL 
   SELECT ID_GRADE,TOTAL_GRADE AS GRADE,NOTE 
   FROM table_grade_a 'tgb'
   )temp_grade ON tg.ID_GRADE = temp_grade.ID_GRADE 
where tg.rn = 1

# and tg.STUDENT = 'MIYA'

当前方法的问题是，您选择的是max date，一个表级聚合，同时还请求所有单个记录。这确实有道理。一个正确的可能性是 LIMIT 与 ORDER BY :

SELECT tg1.STUDENT, tg1.TEST_DATE, tg2.*
FROM table_grade tg1
INNER JOIN
(
    SELECT ID_GRADE, GRADE, NOTE
    FROM table_grade_a
    UNION ALL 
    SELECT ID_GRADE, TOTAL_GRADE, NOTE 
    FROM table_grade_b
) tg2
    ON tg1.ID_GRADE = tg2.ID_GRADE 
WHERE
    tg1.STUDENT = 'MIYA'
ORDER BY
    tg1.TEST_DATE DESC
LIMIT 1;