每个题目和每份试卷被作答的人数和次数

【分类】：合并查询

分析思路

难点：
1.MySQL union 和 order by 同时使用需要注意的问题

扩展：

前往查看：MySQL union 和 order by 一起使用需要注意的问题

错误的情况

（1）使用 union 和 多个order by 不加括号 报错

（2）order by 在 union 连接的子句不起作用，但是在子句的子句中起作用

解决上述问题的两种方法

（1）先使用 union 后使用order by

（2）order by 在 union 连接的子句的子句中使用

常见的错误写法

（1）使用 union 和 多个order by 不加括号 【报错】

select 
    exam_id as tid,
    count(distinct uid) as uv,
    count(uid) as pv
from exam_record a
group by exam_id
order by uv desc, pv desc
union
select 
    question_id as tid,
    count(distinct uid) as uv,
    count(uid) as pv
from practice_record b
group by question_id
order by uv desc, pv desc

执行出错

程序异常退出, 请检查代码"是否有数组越界等异常"或者"是否有语法错误"
SQL_ERROR_INFO: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'union all\n\nselect \n question_id as tid,\n count(distinct uid) as uv,\n co' at line 11"

这是因为union在没有括号的情况下只能有一个order by。为什么只能有一个order by 呢？

既然不加括号出错，那我就加上括号使用2个order by !

（2）order by 在 union 连接的子句不起作用，但是在子句的子句中起作用

(select 
    exam_id as tid,
    count(distinct uid) as uv,
    count(uid) as pv
from exam_record a
group by exam_id
order by uv desc, pv desc)
union
(select 
    question_id as tid,
    count(distinct uid) as uv,
    count(uid) as pv
from practice_record b
group by question_id
order by uv desc, pv desc)

结果发现这样写order by在每个子表中不起作用！所以这种方法也不对。但是 union 可以在子句的子句中起作用，写成下面这样：

方法一：

#正确代码
select * from 
(
select 
    exam_id as tid,
    count(distinct uid) as uv,
    count(uid) as pv
from exam_record a
group by exam_id
order by uv desc, pv desc
) a
union
select * from 
(
select 
    question_id as tid,
    count(distinct uid) as uv,
    count(uid) as pv
from practice_record b
group by question_id
order by uv desc, pv desc
) attr

那我是不是可以union两个子句之后再使用order by ，但是这个排序要对2个表分别进行降序，就需要写成下面这样：

方法二：

使用函数

left(str,length) 函数： str左边开始的长度为 length 的子字符串，在本例中为‘9’和‘8’
解释：试卷编号以‘9’开头、题目编号以‘8’开头，对编号进行降序就是对"试卷"和"题目"分别进行排序

(
    #每份试卷被作答的人数和次数
    select
        exam_id as tid,
        count(distinct uid) as uv,
        count(*) as pv
from exam_record
group by exam_id
)
union
(
    #每个题目被作答的人数和次数
    select
        question_id as tid,
        count(distinct uid) as uv,
        count(*) as pv
from practice_record
group by question_id
)
#分别按照"试卷"和"题目"的uv & pv降序显示
order by left(tid,1) desc,uv desc,pv desc

(select exam_id as tid,count(distinct uid) as uv,count(*) as pv
from exam_record
group by exam_id)
union
(select question_id as tid,count(distinct uid),count(*)
from practice_record
group by question_id)
order by left(tid,1) desc,uv desc,pv desc

select t.tid ,
count(distinct uid) as uv,
count(*)
from (
select uid,exam_id as tid from exam_record
union 
select uid,question_id from practice_record)t
group by t.tid 

由于这道题要求对 试卷和练习的结果分别排序，所以上方的写法就没有办法实现了。

with t1 as 

(select  exam_id as tid , count(distinct uid) as uv ,
count(*) as pv
from exam_record
group by tid order by uv desc,pv desc),
t2 as 
(select  question_id as tid , count(distinct uid) as uv ,
count(*) as pv
from practice_record
group by tid order by uv desc ,pv desc)

select * from t1 
union 
select * from t2

由于union 的排序最能跟在最后的语句中，即只出现一次。

所以用临时表的方式，或者在每个union 外面再加一层查询都是可以的。

这题的有点刁钻了。

select
exam_id tid
,count(distinct uid) uv
,count(*) pv
from exam_record
group by tid
union all
select
question_id tid
,count(distinct uid) uv
,count(*) pv
from practice_record
group by tid
order by left(tid,1) desc,uv desc,pv desc
#分组排序的时候只需要把分组依据也加到order by

SELECT a.tid,a.uv,a.pv
FROM(    
    SELECT exam_id as tid,count(distinct uid) as uv,count(id) as pv
    FROM exam_record
    GROUP BY exam_id
    UNION ALL
    SELECT question_id as tid,count(distinct uid) as uv,count(id) as pv
    FROM practice_record
    GROUP BY question_id) a
ORDER BY left(a.tid,1) desc,a.uv desc,a.pv desc

select exam_id tid,count(distinct uid) UV,count(*) PV
from exam_record
group by tid 
union all
select question_id tid,count(distinct uid) UV,count(*) PV
from practice_record 
group by tid
order by left(tid,1) desc,UV desc,PV desc

select *
from(
select exam_id tid,count(distinct uid) uv,count(1) pv
from exam_record
group by 1
order by 2 desc,3 desc
) a
union all
select *
from(
select question_id tid,count(distinct uid) uv,count(1) pv
from practice_record 
group by 1
order by 2 desc,3 desc
) b

with a as(
select exam_id tid,count(distinct uid) uv,count(1) pv
from exam_record
group by 1
order by 2 desc,3 desc
), 
b as(
select question_id tid,count(distinct uid) uv,count(1) pv
from practice_record 
group by 1
order by 2 desc,3 desc
)
select *
from a
union all
select *
from b

# 请统计每个题目和每份试卷被作答的人数和次数，分别按照"试卷"和"题目"的uv & pv降序显示，示例数据结果输出如下：

-- 注意最后输出结果排序
select tid,uv,pv
from 
(
select exam_id as tid,count(distinct uid) uv,count(uid) pv from exam_record group by exam_id 
union all 
select question_id as tid,count(distinct uid) uv,count(uid) pv from practice_record group by question_id 
) tt
order by substr(tid,1,1) desc,  pv desc

(
  select
    exam_id as tid,
    count(distinct uid) as uv,
    count(id) as pv
  from
    exam_record
  group by
    exam_id
  order by
    uv desc,
    pv desc
  limit
    9999
)
union all
(
  select
    question_id as tid,
    count(distinct uid) as uv,
    count(id) as pv
  from
    practice_record
  group by
    question_id
  order by
    uv desc,
    pv desc
  limit
    9999
)

with a as (
select 
exam_id,
count(distinct uid) as uv,
count(uid) as pv
from exam_record 
group by exam_id
order by uv desc,pv desc),
b as (
select 
question_id,
count(distinct uid) as uv,
count(uid) as pv
from practice_record
group by question_id
order by uv desc,pv desc)


select * from a 
union all
select * from b