sql多表查询之合并查询（union）题1
解1
(select
exam_id as tid,
count(distinct uid)as uv,
count(exam_id)as pv
FROM
exam_record
group by
exam_id)
union all# 不去重
(select
question_id as tid,
count(distinct uid)as uv,
count(question_id)as pv
FROM
practice_record
group by
question_id)
order BY
LEFT(tid,1)desc,
uv desc,
pv DESC;
解2
from
(select
exam_id as tid,
count(distinct uid)as uv,
count(*)as pv
from
exam_record
group by
exam_id
order by
uv desc,
pv desc)as exam
union
select
*
from
(select
question_id as tid,
count(distinct uid)as uv,
count(*)as pv
from
practice_record
group by
question_id
order by
uv desc,
pv desc)as practice # 注意⼦表必须有表名。
解3
⽤tid字段的左边第⼀个数来排序。
要注意的是关于UNION后的排序问题，要么在UNION之前分别单独排序（如上解法），要么在union之后再排序：
(SELECT
exam_id AS tid,
COUNT(DISTINCT exam_record.uid) uv,
COUNT(*) pv
FROM
exam_record
GROUP BY
exam_id)
UNION
(SELECT
question_id AS tid,
COUNT(DISTINCT practice_record.uid) uv,
COUNT(*) pv
FROM
practice_record
GROUP BY
question_id)
ORDER BY LEFT(tid,1)DESC, uv DESC, pv DESC;
题2
'activity1'as activity
from
exam_record
where
year(submit_time)=2021
group by
uid
having
min(score)>=85# 对于每组要求最⼩得分不⼩于85，过滤
union all# 将两个筛选合并。不去重
select
distinct uid,
'activity2'as activity
from
exam_record e_r join examination_info e_i
on am_id = am_id
where
year(e_r.submit_time)=2021
and e_i.difficulty ='hard'
and e_r.score >80
and timestampdiff(minute, e_r.start_time, e_r.submit_time)*2< e_i.duration order by
uid
或：
select
uid,
'activity1'as activity
from
exam_record
where
year(submit_time)=2021
group by
uid
having
min(score)>=85# 对于每组要求最⼩得分不⼩于85，过滤
union all# 将两个筛选合并。不去重
select
distinct uid,
'activity2'as activity
from
exam_record e_r join examination_info e_i
on am_id = am_id
where
year(submit_time)=2021
sql中union多表合并and difficulty ='hard'
and score >80
and timestampdiff(minute, start_time, submit_time)*2< duration
order by
uid

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。