MySQL两个查询如何合并成一个结果详解

吾爱主题阅读：140 2024-04-01 23:22:10 评论：0

MySQL 查询合并

如果我们需要将两个select语句的结果作为一个整体显示出来，我们就需要用到union或者union all关键字。union(或称为联合)的作用是将多个结果合并在一起显示出来。

注意：两个列表中的字段要一样才可以合并(顺序也要一样)

满足条件：

1、两个select查询的列的数量必须相同；

2、每个列的数据类型需要相似；

1.先写两条select

第一条select：

1 2 3 4 5 6 7 8 9 10 SELECT DATE_FORMAT(add_time, '%Y-%m-%d' ) as 'add_time' , COUNT (add_time) as 'reach_intention' FROM table1 where ## where 条件可以根据自己实际情况来定 DATE_FORMAT(add_time, '%Y-%m-%d' ) > DATE_FORMAT(date_sub(now(), interval 1 month ), '%Y-%m-%d' ) group by DATE_FORMAT(add_time, '%Y-%m-%d' )

这是查询出来的结果

第二条select：

1 2 3 4 5 6 7 8 9 10 select DATE_FORMAT(add_date, '%Y-%m-%d' ) as 'add_time' , COUNT (add_date) as 'post_release' from table2 where ## where 条件可以根据自己实际情况来定 DATE_FORMAT(add_date, '%Y-%m-%d' ) > DATE_FORMAT(date_sub(now(), interval 1 month ), '%Y-%m-%d' ) group by DATE_FORMAT(add_date, '%Y-%m-%d' )

第二条select查询出来的结果

2.合并查询结果

先把两条select用union all连接起来

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 SELECT DATE_FORMAT(add_time, '%Y-%m-%d' ) as 'add_time' , COUNT (add_time) as 'reach_intention' , '' as 'post_release' FROM table1 where DATE_FORMAT(add_time, '%Y-%m-%d' ) > DATE_FORMAT(date_sub(now(), interval 30 day ), '%Y-%m-%d' ) group by DATE_FORMAT(add_time, '%Y-%m-%d' ) UNION ALL select DATE_FORMAT(add_date, '%Y-%m-%d' ) as 'add_time' , '' as 'reach_intention' , COUNT (add_date) as 'post_release' from table2 where DATE_FORMAT(add_date, '%Y-%m-%d' ) > DATE_FORMAT(date_sub(now(), interval 30 day ), '%Y-%m-%d' ) group by DATE_FORMAT(add_date, '%Y-%m-%d' )

在外层嵌套一个select

1 2 3 ## 括号里面放用 union all 连接的 select select * from () test ## 这里要给表起个别名不然会报 Every derived table must have its own alias 每个派生表都必须有自己的别名

把union all 连接的select 放到括号里面去然后运行

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 select * from ( SELECT DATE_FORMAT(add_time, '%Y-%m-%d' ) as 'add_time' , COUNT (add_time) as 'reach_intention' , '' as 'post_release' FROM table1 where DATE_FORMAT(add_time, '%Y-%m-%d' ) > DATE_FORMAT(date_sub(now(), interval 30 day ), '%Y-%m-%d' ) group by DATE_FORMAT(add_time, '%Y-%m-%d' ) UNION ALL select DATE_FORMAT(add_date, '%Y-%m-%d' ) as 'add_time' , '' as 'reach_intention' , COUNT (add_date) as 'post_release' from table2 where DATE_FORMAT(add_date, '%Y-%m-%d' ) > DATE_FORMAT(date_sub(now(), interval 30 day ), '%Y-%m-%d' ) group by DATE_FORMAT(add_date, '%Y-%m-%d' ) ) test

运行结果

把结果按日期分组排序。

使用 group by 和 order by 关键字

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 select add_time, sum (reach_intention) as 'reach_intention' , sum (post_release) as 'post_release' from ( SELECT DATE_FORMAT(add_time, '%Y-%m-%d' ) as 'add_time' , COUNT (add_time) as 'reach_intention' , '' as 'post_release' FROM table1 where DATE_FORMAT(add_time, '%Y-%m-%d' ) > DATE_FORMAT(date_sub(now(), interval 30 day ), '%Y-%m-%d' ) group by DATE_FORMAT(add_time, '%Y-%m-%d' ) UNION ALL select DATE_FORMAT(add_date, '%Y-%m-%d' ) as 'add_time' , '' as 'reach_intention' , COUNT (add_date) as 'post_release' from table2 where DATE_FORMAT(add_date, '%Y-%m-%d' ) > DATE_FORMAT(date_sub(now(), interval 30 day ), '%Y-%m-%d' ) group by DATE_FORMAT(add_date, '%Y-%m-%d' ) ) test group by test.add_time order by test.add_time desc