select s.name,a.cnt
from(
    select subject_id, count(*) cnt
    from submission 
    where create_time=date_format(now(),'%Y-%m-%d')
    group by subject_id) a
left join 
subject s
on a.subject_id=s.id
order by a.cnt desc,a.subject_id
  • 先聚合得到各题目的次数
  • 然后连接
  • 使用current_date 或者 now 来得到当前日期