-- 看着简简单单 但是我写的很复杂
WITH subnew AS (
SELECT
submission.id,subject_id,create_time,name
FROM submission
LEFT JOIN subject s
ON s.id = submission.subject_id
WHERE create_time = current_date
)
SELECT
t1.name,t1.cnt
FROM (
SELECT DISTINCT name,count(subject_id) OVER (PARTITION BY name ORDER BY subject_id) cnt,subject_id
FROM subnew
ORDER BY cnt DESC ,subject_id
)t1;