题解 | #今天的刷题量(一)#

-- 看着简简单单 但是我写的很复杂

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;