sql多表查询之合并查询（union）

题1

https://www.nowcoder.com/practice/203d0aed8928429a8978185d9a03babc?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0

解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

select 
    * 
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

https://www.nowcoder.com/practice/a126cea91d7045e399b8ecdcadfb326f?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0

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 e_r.exam_id = e_i.exam_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 e_r.exam_id = e_i.exam_id
where 
    year(submit_time) = 2021
    and difficulty = 'hard'
    and score > 80
    and timestampdiff(minute, start_time, submit_time) * 2 < duration
order by 
    uid