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

MySQL 查詢(xún)合并

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

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

滿足條件：

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

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

1.先寫(xiě)兩條select

第一條select：

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

這是查詢(xún)出來(lái)的結(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ù)自己實(shí)際情況來(lái)定
	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查詢(xún)出來(lái)的結(jié)果

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

先把兩條select用union all連接起來(lái)

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')

在外層嵌套一個(gè)select

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

把union all 連接的select 放到括號(hào)里面去然后運(yùn)行

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

運(yùn)行結(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é)果如下

總結(jié)

到此這篇關(guān)于MySQL兩個(gè)查詢(xún)?nèi)绾魏喜⒊梢粋€(gè)結(jié)果的文章就介紹到這了,更多相關(guān)MySQL兩個(gè)查詢(xún)合并一個(gè)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: