先上代码

SELECT
    e_r.exam_id,
    count(DISTINCT e_r.uid) uv,
    ROUND(AVG(score),1) avg_score
FROM
    exam_record e_r
JOIN(
    select
        uid
    FROM
        user_info
    WHERE 
        level > 5
    )u
ON
    u.uid = e_r.uid
JOIN
(
    SELECT
        exam_id,
        release_time
    FROM
        examination_info 
    WHERE
        tag = 'SQL'
)e_i
ON
    e_r.exam_id = e_i.exam_id
WHERE
    DATE(submit_time) = DATE(release_time)
GROUP BY
    e_r.exam_id
ORDER BY
    uv DESC,avg_score

或者跟昨天那个题一样可以用where而不是表连接来处理

select 
  exam_id,
  count(distinct uid)  uv,
  round(avg(score),1)  avg_score
from 
  exam_record
where 
  exam_id in(
    select 
      exam_id 
    from 
      examination_info 
    where 
       tag = 'SQL'
    ) 
  and 
    uid in(
        select 
          uid 
        from 
          user_info 
        where 
          level >5
        )
  and 
    date(submit_time) 
    in(
       select 
        date(release_time) 
       from 
        examination_info
      )
  and 
    submit_time is not NULL
group by 
  exam_id,date(submit_time)
order by
  uv desc,avg_score 

不难没啥不太清楚的旧知识跟新知识。