mysql-union all 和order by 问题

最新推荐文章于 2024-01-16 15:45:46 发布
guoyiguang2
最新推荐文章于 2024-01-16 15:45:46 发布
阅读量2.8k
点赞数
分类专栏： mysql 文章标签： sql
本文链接：https://blog.csdn.net/qq_38807606/article/details/124321301
版权
mysql 专栏收录该内容
33 篇文章 0 订阅
订阅专栏
# 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 |
# +----+--+--+
guoyiguang2
关注
0
点赞
踩
5

收藏

觉得还不错? 一键收藏
1
评论
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 t.
复制链接

扫一扫