with b1 as (select date_format(now(),"%Y-%m-%d") create_time)

select name,count(*) cnt
from 
(select subject_id id,
date_format(create_time,"%Y-%m-%d") create_time  -- 将日期格式化
from submission)b2 join b1 using(create_time) -- 和今日日期作内连接
join subject using(id) -- 连接提单表
group by name,id
order by cnt desc,id