[大数据] mysql-union all 和order by 问题

开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> mysql-union all 和order by 问题 -> 正文阅读

[大数据]mysql-union all 和order by 问题

# union all  order by 排序问题 ，order by 只可以存在 在最后一个子句中 对全部的结果集排序

# 如果想要 对 各个子句排序，需要 对各个子句 order by 之后 作为新的 临时表 再 union all
# SELECT * FROM
# ( SELECT * FROM t1  ORDER BY 字段A limit 0, 1000 ) newt1 ## 一定要对表重新命名，否则报错
# UNION
# SELECT * FROM
# ( SELECT * FROM t2  ORDER BY 字段B limit 0, 1000 ) newt2

# 说明：不加 limit 0, 1000  排序是失效，因为 子查询规则是没有top语句（mysql中用limit代替）



# 问题： a union all b 和  b union all a 结果一样吗？ 不一样 ，前者 a 在上面 ，b在下面，后者反过来

# demo

select name0 from girl  ;

# +-----+
# |name0|
# +-----+
# |ga   |
# |gb   |
# |gc   |
# |gd   |
# |ge   |
# |gf   |
# |gg   |
# |gh   |
# |gi   |
# +-----+


select name0 from boy  ;
# +-----+
# |name0|
# +-----+
# |ba   |
# |bb   |
# |bc   |
# |bd   |
# +-----+


# 需求①：将girl 表数据和 boy 表数据 合并起来，然后整体 进行 排序（降序）

select name0
from girl
union all
select name0
from boy   ;

# +-----+
# |name0|
# +-----+
# |ga   |
# |gb   |
# |gc   |
# |gd   |
# |ge   |
# |gf   |
# |gg   |
# |gh   |
# |gi   |
# |ba   |
# |bb   |
# |bc   |
# |bd   |
# +-----+


select name0
from girl
union all
select name0
from boy order by name0 desc ;

# +-----+
# |name0|
# +-----+
# |gi   |
# |gh   |
# |gg   |
# |gf   |
# |ge   |
# |gd   |
# |gc   |
# |gb   |
# |ga   |
# |bd   |
# |bc   |
# |bb   |
# |ba   |
# +-----+

# 需求② ：  girl 表降序 ，boy 表升序  将结果集合并（注意：实际结果和预测结果不一样）


select *
from (select name0
      from girl
      order by name0 desc) tem1
union all

select *
from (select name0
      from boy
      order by name0 asc) tem2;


# +-----+
# |name0|
# +-----+
# |ga   |
# |gb   |
# |gc   |
# |gd   |
# |ge   |
# |gf   |
# |gg   |
# |gh   |
# |gi   |
# |ba   |
# |bb   |
# |bc   |
# |bd   |
# +-----+

#

select name0
from girl
union all
select name0
from boy
order by name0 desc,
 name0 asc;

# +-----+
# |name0|
# +-----+
# |gi   |
# |gh   |
# |gg   |
# |gf   |
# |ge   |
# |gd   |
# |gc   |
# |gb   |
# |ga   |
# |bd   |
# |bc   |
# |bb   |
# |ba   |
# +-----+


# 发现排序失败 因为子查询规则是没有top语句（mysql中用limit代替） limit 0, 1000


select *
from (select name0
      from girl
      order by name0 desc limit 0, 1000) tem1
union all

select *
from (select name0
      from boy
      order by name0 asc limit 0, 1000 ) tem2;

# +-----+
# |name0|
# +-----+
# |gi   |
# |gh   |
# |gg   |
# |gf   |
# |ge   |
# |gd   |
# |gc   |
# |gb   |
# |ga   |
# |ba   |
# |bb   |
# |bc   |
# |bd   |
# +-----+



# 需求③ ：  girl 表降序 ，boy 表升序  将结果集合并 ，再整体降序

select *
from (select name0
      from girl
      order by name0 desc) tem1
union all

select *
from (select name0
      from boy
      order by name0 asc) tem2 order by  name0 desc ;

# +-----+
# |name0|
# +-----+
# |gi   |
# |gh   |
# |gg   |
# |gf   |
# |ge   |
# |gd   |
# |gc   |
# |gb   |
# |ga   |
# |bd   |
# |bc   |
# |bb   |
# |ba   |
# +-----+


# 需求④： a union all b 和  b union all a 结果一样吗？ 不一样

select name0
from girl
union all
select name0
from boy;

# +-----+
# |name0|
# +-----+
# |ga   |
# |gb   |
# |gc   |
# |gd   |
# |ge   |
# |gf   |
# |gg   |
# |gh   |
# |gi   |
# |ba   |
# |bb   |
# |bc   |
# |bd   |
# +-----+


select name0
from boy
union all
select name0
from girl ;

# +-----+
# |name0|
# +-----+
# |ba   |
# |bb   |
# |bc   |
# |bd   |
# |ga   |
# |gb   |
# |gc   |
# |gd   |
# |ge   |
# |gf   |
# |gg   |
# |gh   |
# |gi   |
# +-----+




# 实战
drop table if exists practice_record;
CREATE TABLE practice_record
(
    id          int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid         int NOT NULL COMMENT '用户ID',
    question_id int NOT NULL COMMENT '题目ID',
    submit_time datetime COMMENT '提交时间',
    score       tinyint COMMENT '得分'
) CHARACTER SET utf8
  COLLATE utf8_general_ci;

drop table if exists exam_record;
CREATE TABLE exam_record
(
    id          int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid         int      NOT NULL COMMENT '用户ID',
    exam_id     int      NOT NULL COMMENT '试卷ID',
    start_time  datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score       tinyint COMMENT '得分'
) CHARACTER SET utf8
  COLLATE utf8_general_ci;

INSERT INTO practice_record(uid, question_id, submit_time, score)
VALUES (1001, 8001, '2021-08-02 11:41:01', 60),
       (1002, 8001, '2021-09-02 19:30:01', 50),
       (1002, 8001, '2021-09-02 19:20:01', 70),
       (1002, 8002, '2021-09-02 19:38:01', 70),
       (1003, 8001, '2021-08-02 19:38:01', 70),
       (1003, 8001, '2021-08-02 19:48:01', 90),
       (1003, 8002, '2021-08-01 19:38:01', 80);

INSERT INTO exam_record(uid, exam_id, start_time, submit_time, score)
VALUES (1001, 9001, '2021-09-01 09:01:01', '2021-09-01 09:41:01', 81),
       (1002, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 70),
       (1002, 9001, '2021-09-01 19:01:01', '2021-09-01 19:40:01', 80),
       (1002, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 70),
       (1004, 9001, '2021-09-01 19:01:01', '2021-09-01 19:40:01', 85),
       (1002, 9002, '2021-09-01 12:01:01', null, null);


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

select *
from practice_record;

# +--+----+-----------+-------------------+-----+
# |id|uid |question_id|submit_time        |score|
# +--+----+-----------+-------------------+-----+
# |1 |1001|8001       |2021-08-02 11:41:01|60   |
# |2 |1002|8001       |2021-09-02 19:30:01|50   |
# |3 |1002|8001       |2021-09-02 19:20:01|70   |
# |4 |1002|8002       |2021-09-02 19:38:01|70   |
# |5 |1003|8001       |2021-08-02 19:38:01|70   |
# |6 |1003|8001       |2021-08-02 19:48:01|90   |
# |7 |1003|8002       |2021-08-01 19:38:01|80   |
# +--+----+-----------+-------------------+-----+

select *
from exam_record;

# +--+----+-------+-------------------+-------------------+-----+
# |id|uid |exam_id|start_time         |submit_time        |score|
# +--+----+-------+-------------------+-------------------+-----+
# |1 |1001|9001   |2021-09-01 09:01:01|2021-09-01 09:41:01|81   |
# |2 |1002|9002   |2021-09-01 12:01:01|2021-09-01 12:31:01|70   |
# |3 |1002|9001   |2021-09-01 19:01:01|2021-09-01 19:40:01|80   |
# |4 |1002|9002   |2021-09-01 12:01:01|2021-09-01 12:31:01|70   |
# |5 |1004|9001   |2021-09-01 19:01:01|2021-09-01 19:40:01|85   |
# |6 |1002|9002   |2021-09-01 12:01:01|NULL               |NULL |
# +--+----+-------+-------------------+-------------------+-----+


# 根据 exam_id 分组统计 每个试卷出现的次数（被作答的次数） ， distinct 对组内的某个字段去重  tid	uv	pv
select er.exam_id tid, count(*) pv, count(distinct er.uid) uv
from exam_record er
group by er.exam_id;

# +-------+--------+----------------------+
# |exam_id|count(*)|count(distinct er.uid)|
# +-------+--------+----------------------+
# |9001   |3       |3                     |
# |9002   |3       |1                     |
# +-------+--------+----------------------+

# 题目也类似： tid	uv	pv

select pr.question_id tid, count(*) pv, count(distinct uid) uv
from practice_record pr
group by pr.question_id;

# union  all 拼接结果

select er.exam_id tid,  count(distinct er.uid) uv , count(*) pv
from exam_record er
group by er.exam_id
union  all
select pr.question_id tid,  count(distinct uid) uv , count(*) pv
from practice_record pr
group by pr.question_id order by  count(distinct uv) desc ,  count(*) desc ;



# 只能在union的最后一个子查询中使用order by，而这个order by是针对整个unioning后的结果集的
# 错误demo（错误原因：union all 子句 有两个地方出现了  order by）
select er.exam_id tid,  count(distinct er.uid) uv , count(*) pv
from exam_record er
group by er.exam_id order by  count(distinct er.uid) desc ,  count(*) desc
union  all
select pr.question_id tid,  count(distinct uid) uv , count(*) pv
from practice_record pr
group by pr.question_id order by  count(distinct uv) desc ,  count(*) desc ;



# 正确demo(只可以在最后一个子句中使用 order by)

select er.exam_id tid,  count(distinct er.uid) uv , count(*) pv
from exam_record er
group by er.exam_id
union  all
select pr.question_id tid,  count(distinct uid) uv , count(*) pv
from practice_record pr
group by pr.question_id order by  uv desc ,  pv desc ;
# +----+--+--+
# |tid |uv|pv|
# +----+--+--+
# |8001|3 |5 |
# |9001|3 |3 |
# |8002|2 |2 |
# |9002|1 |3 |
# +----+--+--+

# UNION后的排序问题，ORDER BY子句只能在最后一次使用。 如果想要在UNION之前分别单独排序，那么需要这样
# SELECT * FROM
# ( SELECT * FROM t1  ORDER BY 字段 ) newt1 ## 一定要对表重新命名，否则报错
# UNION
# SELECT * FROM
# ( SELECT * FROM t2  ORDER BY 字段 ) newt2

select *
from (select er.exam_id tid, count(distinct er.uid) uv, count(*) pv
      from exam_record er
      group by er.exam_id
      order by uv desc, pv desc) tem1

union all

select *
from (select pr.question_id tid, count(distinct uid) uv, count(*) pv
      from practice_record pr
      group by pr.question_id
      order by uv desc, pv desc) tem2;


# left(str,length) 函数： 左边开始的长度为 length 的子字符串
# 因为 union all 只有最后一个子句才可以 order  by

select er.exam_id tid,  count(distinct er.uid) uv , count(*) pv
from exam_record er
group by er.exam_id
union  all
select pr.question_id tid,  count(distinct uid) uv , count(*) pv
from practice_record pr
group by pr.question_id order by  left(tid,1) desc ,  uv desc ,  pv desc ;


# +----+--+--+
# |tid |uv|pv|
# +----+--+--+
# |9001|3 |3 |
# |9002|1 |3 |
# |8001|3 |5 |
# |8002|2 |2 |
# +----+--+--+