快捷導(dǎo)航

MySQL兩個查詢?nèi)绾魏喜⒊梢粋€結(jié)果詳解

更新時間：2022年08月31日 14:17:41 作者：kuku_zhongzi

利用union關(guān)鍵字,可以給出多條select語句,并將它們的結(jié)果組合成單個結(jié)果集,下面這篇文章主要給大家介紹了關(guān)于MySQL兩個查詢?nèi)绾魏喜⒊梢粋€結(jié)果的相關(guān)資料,文中通過圖文介紹的非常詳細(xì),需要的朋友可以參考下

MySQL 查詢合并

如果我們需要將兩個select語句的結(jié)果作為一個整體顯示出來，我們就需要用到union或者union all關(guān)鍵字。union(或稱為聯(lián)合)的作用是將多個結(jié)果合并在一起顯示出來。

注意：兩個列表中的字段要一樣才可以合并(順序也要一樣)

滿足條件：

1、兩個select查詢的列的數(shù)量必須相同；

2、每個列的數(shù)據(jù)類型需要相似；

1.先寫兩條select

第一條select：

SELECT
	DATE_FORMAT(add_time, '%Y-%m-%d') as 'add_time',
	COUNT(add_time) as 'reach_intention'
FROM
	table1
where
## where 條件可以根據(jù)自己實際情況來定
	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')

這是查詢出來的結(jié)果

結(jié)果一

第二條select：

select
	DATE_FORMAT(add_date, '%Y-%m-%d') as 'add_time' ,
	COUNT(add_date)  as 'post_release'
from
	table2
where
## where 條件可以根據(jù)自己實際情況來定
	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查詢出來的結(jié)果

2.合并查詢結(jié)果

先把兩條select用union all連接起來

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

## 括號里面放 用 union all 連接的select 
select * from () test
## 這里要給表起個別名 不然會報 Every derived table must have its own alias 每個派生表都必須有自己的別名

把union all 連接的select 放到括號里面去然后運行

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

運行結(jié)果

把結(jié)果按日期分組排序。

使用 group by 和 order by 關(guān)鍵字

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

結(jié)果如下