先上代码
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
不难没啥不太清楚的旧知识跟新知识。