按一个表的最大值从SQL表中选择[复制]

testid .

join s、 以及一个相关的子查询来过滤预期的测试。

select ... -- enumerate the columns that you want here
from mediator m 
inner join users u on u.userid  = m.userid
inner join tests t on t.test_id = m.testid
where m.testid = (
    select max(m1.test_id)
    from mediator m1
    where m1.userid = m.userid
)

在MySQL8.0中,还可以使用窗口函数:

select ... 
from (
    select m.*, 
        row_number() over(partition by userid order by testid desc) rn 
    from mediator m 
) m
inner join users u on u.userid  = m.userid
inner join tests t on t.test_id = m.testid
where m.rn = 1