sql多表查询之合并查询（union）题1
解1
(lect
exam_id as tid,
count(distinct uid)as uv,
count(exam_id)as pv
FROM
exam_record
书读百遍的下一句group by
exam_id)
union all# 不去重
(lect
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
(lect
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
lect
*
from
(lect
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# 将两个筛选合并。不去重
lect
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
或：
lect
uid,
'activity1'as activity
from
exam_record
where
year(submit_time)=2021
group by
uid
having
min(score)>=85# 对于每组要求最⼩得分不⼩于85，过滤
union all# 将两个筛选合并。不去重
lect
distinct uid,
'activity2'as activity
from
exam_record e_r join examination_info e_i
84年大阅兵on am_id = am_id
where
year(submit_time)=2021
and difficulty ='hard'
and score >80
and timestampdiff(minute, start_time, submit_time)*2< duration
order by
uid
