select t2.name,count(subject_id) as cnt
from submission as t1
left join subject as t2
on t1.subject_id=t2.id
where t1.create_time= current_date 
group by subject_id,t2.name
order by cnt desc,subject_id asc

1.返回当前日期 current_date 或curdate(）

2.分组的时候注意 group by t1.name,t2.subject_id（名字和题单id相同分到一组），不能仅仅subject_id分组