mysql 合并結(jié)果集

mysql查詢時(shí)經(jīng)常會(huì)把多個(gè)查詢結(jié)果集進(jìn)行合并。主要使用UNION 和 UNION ALL。兩者區(qū)別如下：

網(wǎng)站建設(shè)哪家好，找創(chuàng)新互聯(lián)建站！專(zhuān)注于網(wǎng)頁(yè)設(shè)計(jì)、網(wǎng)站建設(shè)、微信開(kāi)發(fā)、微信小程序定制開(kāi)發(fā)、集團(tuán)企業(yè)網(wǎng)站建設(shè)等服務(wù)項(xiàng)目。為回饋新老客戶創(chuàng)新互聯(lián)還提供了黑龍江免費(fèi)建站歡迎大家使用！

一般在不需對(duì)結(jié)果數(shù)據(jù)作去重和排序時(shí)，使用 UNION ALL。

MySQL 中的各種 JOIN

本文主要介紹 SQL 標(biāo)準(zhǔn)中定義的各種連接的意義和區(qū)別，例如，交叉連接（ CROSS JOIN ）、內(nèi)連接（ INNER JOIN ）、外連接（ OUTER JOIN ）、自然連接（ NATURAL JOIN ）等，并結(jié)合例子講解這些連接在 MySQL 中的語(yǔ)法和表現(xiàn)。

從網(wǎng)上的資料看， JOIN 更多翻譯為連接，本文中凡是出現(xiàn)中文“連接”的地方都是指 JOIN 。

本文中用到的所有例子涉及兩張表—— customers 用戶表和 orders 訂單表，其中訂單表中的 cust_id 字段表示用戶的唯一 ID，也就是用戶表的主鍵 cust_id 。兩張表的數(shù)據(jù)如下：

注：兩張表都經(jīng)過(guò)了簡(jiǎn)化，實(shí)際業(yè)務(wù)中這兩張表肯定還包括其他字段。

英文維基百科 JOIN 詞條 對(duì)連接的定義如下：

翻譯過(guò)來(lái)就是，“連接可以根據(jù)一張（自連接）或多張表中的共同值將這些表的列數(shù)據(jù)合并為一個(gè)新的結(jié)果集，標(biāo)準(zhǔn) SQL 定義了五種連接：內(nèi)連接、左外連接、右外連接、全外連接和交叉連接。”

也就是說(shuō)，連接是 SQL 標(biāo)準(zhǔn)中定義的一種組合多張表的方式，當(dāng)然一張表自身也可以和自身組合，稱(chēng)為自連接。連接后得到的結(jié)果集的每一列其實(shí)都來(lái)自用于連接的多張表，不同的連接類(lèi)型只是區(qū)分了這些列具體從哪張表里來(lái)，列里填充的是什么數(shù)據(jù)。

其實(shí)英文維基百科的 JOIN 詞條已經(jīng)把各種連接的類(lèi)型解釋地非常清楚了，非常值得去看一下。

我們來(lái)看一下 SQL 標(biāo)準(zhǔn)中定義的各種連接類(lèi)型，理解各種連接最好的方法就是把需要連接的表想象成集合，并畫(huà)出可以反映集合的交與并的情況的圖——韋恩圖，例如下圖就畫(huà)出了 SQL 中定義的幾種主要連接。

請(qǐng)先仔細(xì)查看一下圖中的內(nèi)容，你可以從中歸納出幾種連接類(lèi)型呢？

雖然圖中畫(huà)了 7 種集合的交并情況，但是總結(jié)起來(lái)，主要是兩種連接類(lèi)型在起作用——內(nèi)連接（ INNER JOIN ）和外連接（ OUTER JOIN ），其中外連接又分為了左外連接（ LEFT OUTER JOIN ）、右外連接（ RIGHT OUTER JOIN ）和全外連接（ FULL OUTER JOIN ）。

下面先簡(jiǎn)單介紹一下 SQL 標(biāo)準(zhǔn)中各種連接的定義，然后在「MySQL 中的連接」一節(jié)再用例子來(lái)演示 MySQL 中支持的各種連接。

連接既然是用來(lái)合并多張表的，那么要定義一個(gè)連接就必須指定需要連接的表，并指定可選的連接條件。例如，一個(gè)典型的 SQL 連接語(yǔ)句如下：

我們用表 A 和表 B 指代需要連接的兩張表，經(jīng)過(guò) 內(nèi)連接 后得到的結(jié)果集 僅 包含所有滿足 連接條件 的數(shù)據(jù)；而經(jīng)過(guò) 外連接 后得到的數(shù)據(jù)集 不僅 包含滿足 連接條件 的數(shù)據(jù)，還包含其他數(shù)據(jù)，具體的差別是：

在上面「SQL 標(biāo)準(zhǔn)定義的主要連接」一圖中并沒(méi)有列出交叉連接，交叉連接會(huì)對(duì)連接的兩張表做笛卡爾積，也就是連接后的數(shù)據(jù)集中的行是由第一張表中的每一行與第二張表中的每一行配對(duì)而成的，而不管它們 邏輯上 是否可以搭配在一起。假設(shè)交叉連接的兩張表分別有 m 和 n 行數(shù)據(jù)，那么交叉連接后的數(shù)據(jù)集就包含 m 乘以 n 行數(shù)據(jù)。

連接根據(jù)連接的條件不同，又可以區(qū)分為等值連接和非等值連接，「SQL 標(biāo)準(zhǔn)定義的主要連接」圖中畫(huà)出的連接的連接條件都是比較兩個(gè)字段是否相等，它們都是等值連接。

自然連接是等值連接的一種特殊形式，自然連接會(huì)自動(dòng)選取需要連接的兩張表中字段名相同的 所有 列做相等比較，而不需要再指定連接條件了。

注：以下內(nèi)容全部基于 MySQL 5.7 版本，所有例子只保證在 MySQL 5.7 上是可以正確執(zhí)行的。

MySQL 中支持的連接類(lèi)型和關(guān)鍵字如下：

上面的表示方法摘自 MySQL 5.7 版本 官方文檔 ，其中 | 表示兩者皆可出現(xiàn)， [] 表示的是可選的， {} 表示的是必選的，例如 NATURAL LEFT JOIN 和 NATURAL JOIN 都是合法的。

可以看到，除了全外連接（ FULL OUTER JOIN ）以外， MySQL 基本支持了 SQL 標(biāo)準(zhǔn)中定義的各種連接。在 MySQL 中全外連接可以通過(guò) UNION 合并的方式做到，當(dāng)然前提是你知道自己為什么需要這么做，具體參見(jiàn)： Full Out Join in MySQL 。

MySQL 語(yǔ)法中還支持一個(gè)并不在 SQL 標(biāo)準(zhǔn)中的 STRAIGHT_JOIN ，它在 表現(xiàn)上 和內(nèi)連接或者交叉連接并無(wú)區(qū)別，只是一種給 MySQL 優(yōu)化器的一個(gè)提示， STRAIGHT_JOIN 提示 MySQL 按照語(yǔ)句中表的順序加載表，只有在你明確清楚 MySQL 服務(wù)器對(duì)你的 JOIN 語(yǔ)句做了負(fù)優(yōu)化的時(shí)候才可能用到它。

還有一點(diǎn)需要說(shuō)明的是，根據(jù) 官方文檔 ，在 MySQL 中， JOIN 、 CROSS JOIN 和 INNER JOIN 實(shí)現(xiàn)的功能是一致的，它們?cè)谡Z(yǔ)法上是等價(jià)的。從語(yǔ)義上來(lái)說(shuō)， CROSS JOIN 特指無(wú)條件的連接（沒(méi)有指定 ON 條件的 JOIN 或者沒(méi)有指定 WHERE 連接條件的多表 SELECT ）， INNER JOIN 特指有條件的連接（指定了 ON 條件的 JOIN 或者指定了 WHERE 連接條件的多表 SELECT ）。當(dāng)然，如果你非要寫(xiě) ... CROSS JOIN ... ON ... 這樣的語(yǔ)法，也是可以執(zhí)行的，雖然寫(xiě)著交叉連接，實(shí)際上執(zhí)行的是內(nèi)連接。

下面我們就用例子來(lái)看一看 MySQL 中支持的幾種連接的例子。

注：下面的例子都沒(méi)有指定 ORDER BY 子句，返回結(jié)果的順序可能會(huì)因?yàn)閿?shù)據(jù)插入順序的不同而略有不同。

MySQL 的交叉連接或內(nèi)連接有兩種寫(xiě)法，一種是使用 JOIN 并用 ON 或者 USING 子句指定連接條件的寫(xiě)法，一種是普通的 SELECT 多表，并且用 WHERE 子句指定連接的鍵的寫(xiě)法。

下面的例子是一個(gè)交叉連接：

上面的寫(xiě)法等價(jià)于：

當(dāng)然，第二種寫(xiě)法中如果將 CROSS JOIN 替換成 JOIN 或者 INNER JOIN 也是可以正確執(zhí)行的。上面兩條語(yǔ)句的執(zhí)行結(jié)果如下：

可以看到共返回了 30 行結(jié)果，是兩張表的笛卡爾積。

一個(gè)內(nèi)連接的例子如下：

上面的寫(xiě)法等價(jià)于：

在連接條件比較的字段相同的情況下，還可以改用 USING 關(guān)鍵字，上面的寫(xiě)法等價(jià)于：

上面三條語(yǔ)句的返回結(jié)果如下：

可以看到只返回了符合連接條件 customers.cust_id = orders.cust_id 的 6 行結(jié)果，結(jié)果的含義是所有有訂單的用戶和他們的訂單。

左外連接和右外連接的例子如下，其中的 OUTER 關(guān)鍵字可以省略：

其中右外連接的返回與內(nèi)連接的返回是一致的（思考一下為什么），左外連接的返回結(jié)果如下：

可以看到一共返回了 8 行數(shù)據(jù)，其中最后兩行數(shù)據(jù)對(duì)應(yīng)的 order_id 的值為 NULL ，結(jié)果的含義是所有用戶的訂單，不管這些用戶是否已經(jīng)有訂單存在了。

根據(jù)前面介紹的自然連接的定義，自然連接會(huì)自動(dòng)用參與連接的兩張表中 字段名相同 的列做等值比較，由于例子中的 customers 和 orders 表只有一列名稱(chēng)相同，我們可以用自然連接的語(yǔ)法寫(xiě)一個(gè)與上面的內(nèi)連接的例子表現(xiàn)行為一樣的語(yǔ)句如下：

可以看到，使用自然連接就不能再用 ON 子句指定連接條件了，因?yàn)檫@完全是多余的。

當(dāng)然，自然連接同樣支持左外連接和右外連接。

下面用一個(gè) customers 表自連接的例子再來(lái)說(shuō)明一下自然連接，語(yǔ)句如下：

因?yàn)槭亲赃B接，因此必須使用 AS 指定別名，否則 MySQL 無(wú)法區(qū)分“兩個(gè)” customers 表，運(yùn)行的結(jié)果如下：

可以看到結(jié)果集和 customers 表完全一致，大家可以思考一下為什么結(jié)果是這樣的。

文章之前也提到了，MySQL 還支持一種 SQL 標(biāo)準(zhǔn)中沒(méi)有定義的“方言”， STRAIGHT_JOIN ， STRAIGHT_JOIN 支持帶 ON 子句的內(nèi)連接和不帶 ON 子句的交叉連接，我們來(lái)看一個(gè) STRAIGHT_JOIN 版本的內(nèi)連接的例子：

返回結(jié)果與前面內(nèi)連接的例子是一致的，如下：

STRAIGHT_JOIN 的表現(xiàn)和 JOIN 是完全一致的，它只是一種給 MySQL 優(yōu)化器的提示，使得 MySQL 始終按照語(yǔ)句中表的順序讀取表（上面的例子中，MySQL 在執(zhí)行時(shí)一定會(huì)先讀取 customers 表，再讀取 orders 表），而不會(huì)做改變讀取表的順序的優(yōu)化。關(guān)于 MySQL 優(yōu)化器的話題這里不做展開(kāi)，需要說(shuō)明的是除非你非常清楚你在做什么，否則不推薦直接使用 STRAIGHT_JOIN 。

你能理解上面的語(yǔ)句是在檢索什么數(shù)據(jù)嗎？

本文主要介紹了 SQL 標(biāo)準(zhǔn)里定義的各種連接的概念，以及 MySQL 中的實(shí)現(xiàn)，并通過(guò)各種例子來(lái)介紹了這些連接的區(qū)別。這些連接不一定都能在實(shí)際開(kāi)發(fā)中用到，但是做到心中有知識(shí)也還是很有必要的。

那么，現(xiàn)在再回憶一下，什么是內(nèi)連接、外連接、自連接、等值連接和自然連接？他們的區(qū)別是什么？

最后，給大家留一個(gè)思考題，為什么 MySQL 中沒(méi)有左外連接或者右外連接版本的 STRAIGHT_JOIN ？

mysql索引（九）索引合并

索引合并是mysql底層為我們提供的智能算法。了解索引合并的算法，有助于我們更好的創(chuàng)建索引。

索引合并是通過(guò)多個(gè)range類(lèi)型的掃描并且合并它們的結(jié)果集來(lái)檢索行的。僅合并來(lái)自單個(gè)表的索引掃描，而不是跨多個(gè)表的索引掃描。合并會(huì)產(chǎn)生底層掃描的三種形式：unions（合并）、intersections（交集）、unions-of-intersections（先取交集再合并）。

以下四個(gè)例子會(huì)產(chǎn)生索引合并：

索引合并有以下已知的局限性：

1、如果查詢語(yǔ)句包含一個(gè)帶有嚴(yán)重AND/OR嵌套的復(fù)雜的WHERE子句而MySQL沒(méi)有選擇最佳計(jì)劃，那么可以嘗試使用以下的標(biāo)志符轉(zhuǎn)換：

(x AND y) OR z = (x OR z) AND (y OR z)

(x OR y) AND z = (x AND z) OR (y AND z)

2、索引合并不適用于全文索引。

在 EXPLAIN 語(yǔ)句輸出的信息中，索引合并在type列中表現(xiàn)為“index_merge”，在這種情況下，key列包含使用的索引列表。

索引合并訪問(wèn)方法有幾種算法，表現(xiàn)在 EXPLAIN 語(yǔ)句輸出的Extra字段中：

下面將更詳細(xì)地描述這些算法。優(yōu)化器根據(jù)各種可用選項(xiàng)的成本估計(jì)，在不同的索引合并算法和其他訪問(wèn)方法之間進(jìn)行選擇。

Index Merge Intersection算法

Index Merge Intersection算法對(duì)所有使用的索引執(zhí)行同步掃描，并生成從合并的索引掃描接收到的行序列的交集。

這種算法適用于當(dāng)WHERE子句被轉(zhuǎn)換成多個(gè)使用AND連接的不同索引key上的范圍條件，且條件是以下兩種之一：

一、這種形式的N部分表達(dá)式，索引正好包括N個(gè)字段（所有索引字段都被覆蓋），N=1，N如果大于1就是復(fù)合索引：

二、InnoDB表主鍵上的任何范圍條件。

例子：

Index Merge Union算法

該算法類(lèi)似于Index Merge Intersection算法，適用于當(dāng)WHERE子句被轉(zhuǎn)換成多個(gè)使用OR連接的不同索引key上的范圍條件，且條件是以下三種之一：

一、這種形式的N部分表達(dá)式，索引正好包括N個(gè)字段（所有索引字段都被覆蓋），N=1，N如果大于1就是復(fù)合索引：

二、InnoDB表主鍵上的任何范圍條件。

三、符合Index Merge Intersection算法的條件。

例子：

Index Merge Sort-Union算法

該算法適用于當(dāng)WHERE子句被轉(zhuǎn)換成多個(gè)使用OR連接的不同索引key上的范圍條件，但是不符合 Index Merge Union算法的。Index Merge Sort-Union和Index Merge Union算法的區(qū)別在于，Index Merge Sort-Union必須首先獲取所有行的行id并在返回任何行之前對(duì)它們進(jìn)行排序。

例子：

有好的建議，請(qǐng)?jiān)谙路捷斎肽愕脑u(píng)論。

歡迎訪問(wèn)個(gè)人博客

mysql如何將2個(gè)結(jié)果集合并成一個(gè)結(jié)果集，列數(shù)，表頭一樣

步驟如下：

select?*?from?(

(SELECT?uid,je,starttime,stoptime,1?as?type?from?(SELECT?id?from?members?WHERE?referer='4')?as?m?JOIN?(SELECT?uid,je,starttime,stoptime?from?`cz`?WHERE?`status`0)?as?c?on?c.uid=m.id)

union

(SELECT?uid,je,starttime,stoptime,2?as?type?from?(SELECT?id?from?members?WHERE?referer='4')?as?m?JOIN?(SELECT?uid,je,starttime,stoptime?from?`withdrawh`?WHERE?`state`=1)?as?w?on?w.uid=m.id)?

)???order?by??starttime

MySQL是一個(gè)關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)，由瑞典MySQL AB 公司開(kāi)發(fā)，目前屬于 Oracle 旗下產(chǎn)品。MySQL 是最流行的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)之一，在 WEB 應(yīng)用方面，MySQL是最好的 RDBMS (Relational Database Management System，關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)) 應(yīng)用軟件。

MySQL是一種關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)，關(guān)系數(shù)據(jù)庫(kù)將數(shù)據(jù)保存在不同的表中，而不是將所有數(shù)據(jù)放在一個(gè)大倉(cāng)庫(kù)內(nèi)，這樣就增加了速度并提高了靈活性。

MySQL所使用的 SQL 語(yǔ)言是用于訪問(wèn)數(shù)據(jù)庫(kù)的最常用標(biāo)準(zhǔn)化語(yǔ)言。MySQL 軟件采用了雙授權(quán)政策，分為社區(qū)版和商業(yè)版，由于其體積小、速度快、總體擁有成本低，尤其是開(kāi)放源碼這一特點(diǎn)，一般中小型網(wǎng)站的開(kāi)發(fā)都選擇 MySQL 作為網(wǎng)站數(shù)據(jù)庫(kù)。

關(guān)于MySQL儲(chǔ)存過(guò)程多個(gè)結(jié)果集如何合并

如果字段相同, 可以直接用 UNION ALL 合并

select a, b,c from tableA where 條件A

union all

select a,b,c from tableA where 條件B

union all

......

mysql 將兩個(gè)SQL語(yǔ)句查詢結(jié)果并在一起

需要將結(jié)果合并到一起顯示出來(lái)，這個(gè)時(shí)候

就需要用到union和union all關(guān)鍵字來(lái)實(shí)現(xiàn)這樣的功能，union和union all的主要區(qū)別是union all是把結(jié)果集直接合并在一起，而

union 是將union all后的結(jié)果進(jìn)行一次distinct，去除重復(fù)的記錄后的結(jié)果。

網(wǎng)頁(yè)題目：mysql怎么合并結(jié)果集,mysql如何合并結(jié)果集
標(biāo)題鏈接：http://chinadenli.net/article43/dsgjihs.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供靜態(tài)網(wǎng)站、域名注冊(cè)、自適應(yīng)網(wǎng)站、用戶體驗(yàn)、企業(yè)網(wǎng)站制作、建站公司

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)