MySQL?where和having的異同

更新時(shí)間：2024年02月07日 08:32:12 作者：安然無(wú)虞

我們?cè)谶M(jìn)行查詢(xún)的時(shí)候,經(jīng)常需要按照條件對(duì)查詢(xún)結(jié)果進(jìn)行篩選,這就要用到條件語(yǔ)句where和having了,本文主要介紹了MySQL?where和having的異同,具有一定的參考價(jià)值,感興趣的可以了解一下

where是直接對(duì)表中的字段進(jìn)行限定來(lái)篩選結(jié)果，having則需要跟分組關(guān)鍵字group by一起使用，通過(guò)對(duì)分組字段或分組計(jì)算函數(shù)進(jìn)行限定來(lái)篩選結(jié)果。雖然它們都是對(duì)查詢(xún)進(jìn)行限定，卻有著各自的特點(diǎn)和適用場(chǎng)景。很多時(shí)候，我們會(huì)遇到2個(gè)都可以用的情況。一旦用錯(cuò)，就容易出現(xiàn)執(zhí)行效率低下、查詢(xún)結(jié)果錯(cuò)誤，甚至是查詢(xún)無(wú)法運(yùn)行的情況。

一個(gè)實(shí)際查詢(xún)需求

超市經(jīng)營(yíng)者提出，要查單筆銷(xiāo)售金額超過(guò)50元的商品。

我們來(lái)分析一下這個(gè)需求：需要查詢(xún)出一個(gè)商品記錄集，限定條件是單筆銷(xiāo)售超過(guò)50元。這個(gè)時(shí)候，我們就需要用到where 和 having了。

這個(gè)問(wèn)題的條件很明確，查詢(xún)的結(jié)果也只有“商品”一個(gè)字段，看起來(lái)挺容易實(shí)現(xiàn)的。

假設(shè)我們有一個(gè)商品信息表demo.goodsmaster，里面有2種商品：書(shū)和筆。

mysql> select *
    -> from demo.goodsmaster;
+------------+---------+-----------+---------------+------+------------+
| itemnumber | barcode | goodsname | specification | unit | salesprice |
+------------+---------+-----------+---------------+------+------------+
|          1 | 0001    | 書(shū)        |               | 本   |      89.00 |
|          2 | 0002    | 筆        |               | 支   |       5.00 |
+------------+---------+-----------+---------------+------+------------+
2 rows in set (0.00 sec)

同時(shí)我們還有一個(gè)商品銷(xiāo)售明細(xì)表demo.transactiondetails，里面有4條銷(xiāo)售記錄：

mysql> select *
    -> from demo.transactiondetails;
+---------------+------------+----------+-------+------------+
| transactionid | itemnumber | quantity | price | salesvalue |
+---------------+------------+----------+-------+------------+
|             1 |          1 |    1.000 | 89.00 |      89.00 |
|             1 |          2 |    2.000 |  5.00 |      10.00 |
|             2 |          1 |    2.000 | 89.00 |     178.00 |
|             3 |          2 |   10.000 |  5.00 |      50.00 |
+---------------+------------+----------+-------+------------+
4 rows in set (0.01 sec)

接下來(lái)我們分別用where和having盡心查詢(xún)，看看它們各自是如何查詢(xún)的，是否能夠得到正確的結(jié)果。

首先用where關(guān)鍵字進(jìn)行查詢(xún)：

mysql> select distinct b.goodsname
-> from demo.transactiondetails as a
-> join demo.goodsmaster as b
-> on (a.itemnumber=b.itemnumber)
-> where a.salesvalue > 50;
+-----------+
| goodsname |
+-----------+
| 書(shū) |
+-----------+
1 row in set (0.00 sec)

接著，使用having 關(guān)鍵字進(jìn)行查詢(xún)：

mysql> select b.goodsname
-> from demo.transactiondetails as a
-> join demo.goodsmaster as b
-> on (a.itemnumber = b.itemnumber)
-> group by b.goodsname
-> having max(a.salesvalue) > 50;
+-----------+
| goodsname |
+-----------+
| 書(shū) |
+-----------+
1 row in set (0.00 sec)

可以看到兩次查詢(xún)的結(jié)果是一樣的，那么這兩種查詢(xún)到底有什么區(qū)別，那個(gè)更好呢？要明白這個(gè)問(wèn)題，首先我們需要先學(xué)習(xí)where和having的執(zhí)行過(guò)程。

where

我們先來(lái)分析一下剛才使用 where 條件的查詢(xún)語(yǔ)句，來(lái)看看 MySQL 是如何執(zhí)行這個(gè)查詢(xún)的。

首先，MySQL 從數(shù)據(jù)表 demo.transactiondetails 中抽取滿(mǎn)足條件“a.salesvalue>50”的記錄：

mysql> select *
    -> from demo.transactiondetails as a
    -> where a.salesvalue > 50;
+---------------+------------+----------+-------+------------+
| transactionid | itemnumber | quantity | price | salesvalue |
+---------------+------------+----------+-------+------------+
|             1 |          1 |    1.000 | 89.00 |      89.00 |
|             2 |          1 |    2.000 | 89.00 |     178.00 |
+---------------+------------+----------+-------+------------+
2 rows in set (0.00 sec)

為了獲取到銷(xiāo)售信息所對(duì)應(yīng)的商品名稱(chēng)，我們需要通過(guò)公共字段“itemnumber”與數(shù)據(jù)表 demo.goodsmaster 進(jìn)行關(guān)聯(lián)，從 demo.goodsmaster 中獲取商品名稱(chēng)：

mysql> select
    ->     a.*, b.goodsname
    -> from
    ->     demo.transactiondetails a
    -> join
    ->     demo.goodsmaster b 
    -> on (a.itemnumber = b.itemnumber)
    -> where
    ->     a.salesvalue > 50;
+---------------+------------+----------+-------+------------+-----------+
| transactionid | itemnumber | quantity | price | salesvalue | goodsname |
+---------------+------------+----------+-------+------------+-----------+
|             1 |          1 |    1.000 | 89.00 |      89.00 | 書(shū)        |
|             2 |          1 |    2.000 | 89.00 |     178.00 | 書(shū)        |
+---------------+------------+----------+-------+------------+-----------+
2 rows in set (0.00 sec)

這個(gè)時(shí)候，如果查詢(xún)商品名稱(chēng)，就會(huì)出現(xiàn)兩個(gè)重復(fù)的記錄：

mysql> select
    ->     b.goodsname
    -> from
    ->     demo.transactiondetails as a
    -> join
    ->     demo.goodsmaster as b on (a.itemnumber = b.itemnumber)
    -> where
    ->     a.salesvalue > 50;
+-----------+
| goodsname |
+-----------+
| 書(shū)        |
| 書(shū)        |
+-----------+
2 rows in set (0.00 sec)

需要注意的是，為了消除重復(fù)的語(yǔ)句，這里我們需要用到一個(gè)關(guān)鍵字：DISTINCT，它的作用是返回唯一不同的值。比如，DISTINCT 字段 1，就表示返回所有字段 1 的不同的值。

下面我們嘗試一下加上 DISTINCT 關(guān)鍵字的查詢(xún)：

mysql> select
    ->     distinct(b.goodsname)  -- 返回唯一不同的值
    -> from
    ->     demo.transactiondetails as a
    ->         join
    ->     demo.goodsmaster as b on (a.itemnumber = b.itemnumber)
    -> where
    ->     a.salesvalue > 50;
+-----------+
| goodsname |
+-----------+
| 書(shū)        |
+-----------+
1 row in set (0.00 sec)

這樣，我們就得到了需要的結(jié)果：?jiǎn)喂P銷(xiāo)售金額超過(guò) 50 元的商品就是“書(shū)”。

總之，WHERE 關(guān)鍵字的特點(diǎn)是，直接用表的字段對(duì)數(shù)據(jù)集進(jìn)行篩選。如果需要通過(guò)關(guān)聯(lián)查詢(xún)從其他的表獲取需要的信息，那么執(zhí)行的時(shí)候，也是先通過(guò) WHERE 條件進(jìn)行篩選，用篩選后的比較小的數(shù)據(jù)集進(jìn)行連接。這樣一來(lái)，連接過(guò)程中占用的資源比較少，執(zhí)行效率也比較高。

having

講完了where，我們?cè)僬f(shuō)說(shuō)having 是如何執(zhí)行的。不過(guò)，在這之前，我們先來(lái)了解一下group by，因?yàn)閔aving不能單獨(dú)使用，必須要跟group by一起使用。

我們可以把group by 理解成對(duì)數(shù)據(jù)進(jìn)行分組，方便我們對(duì)組內(nèi)的數(shù)據(jù)進(jìn)行統(tǒng)計(jì)計(jì)算。

舉個(gè)小例子，來(lái)具體講講group by 如何使用，以及如何在分組里面進(jìn)行統(tǒng)計(jì)計(jì)算。

假設(shè)現(xiàn)在有一組銷(xiāo)售數(shù)據(jù)，我們需要從里面查詢(xún)每天、每個(gè)收銀員的銷(xiāo)售數(shù)量和銷(xiāo)售金額。我們通過(guò)以下的代碼，來(lái)查看一下數(shù)據(jù)的內(nèi)容：

mysql> select *
    -> from demo.transactionhead;
+---------------+------------------+------------+---------------------+
| transactionid | transactionno    | operatorid | transdate           |
+---------------+------------------+------------+---------------------+
|             1 | 0120201201000001 |          1 | 2020-12-10 00:00:00 |
|             2 | 0120201202000001 |          2 | 2020-12-11 00:00:00 |
|             3 | 0120201202000002 |          2 | 2020-12-12 00:00:00 |
+---------------+------------------+------------+---------------------+
3 rows in set (0.00 sec)

mysql> select *
    -> from demo.transactiondetails;
+---------------+------------+----------+-------+------------+
| transactionid | itemnumber | quantity | price | salesvalue |
+---------------+------------+----------+-------+------------+
|             1 |          1 |    1.000 | 89.00 |      89.00 |
|             1 |          2 |    2.000 |  5.00 |      10.00 |
|             2 |          1 |    2.000 | 89.00 |     178.00 |
|             3 |          2 |   10.000 |  5.00 |      50.00 |
+---------------+------------+----------+-------+------------+
4 rows in set (0.01 sec)

mysql> select *
    -> from demo.operator;
+------------+----------+--------+--------------+-------------+---------+--------------------+--------+
| operatorid | branchid | workno | operatorname | phone       | address | pid                | duty   |
+------------+----------+--------+--------------+-------------+---------+--------------------+--------+
|          1 |        1 | 001    | 張靜         | 18612345678 | 北京    | 110392197501012332 | 店長(zhǎng)   |
|          2 |        1 | 002    | 李靜        | 13312345678 | 北京    | 110222199501012332 | 收銀員 |
+------------+----------+--------+--------------+-------------+---------+--------------------+--------+
2 rows in set (0.01 sec)

mysql> select
    -> a.transdate,   -- 交易時(shí)間
    -> c.operatorname,-- 操作員
    -> d.goodsname,   -- 商品名稱(chēng)
    -> b.quantity,    -- 銷(xiāo)售數(shù)量
    -> b.price,       -- 價(jià)格
    -> b.salesvalue   -- 銷(xiāo)售金額
    -> from
    ->   demo.transactionhead as a
    -> join
    ->   demo.transactiondetails as b on (a.transactionid = b.transactionid)
    -> join
    ->  demo.operator as c on (a.operatorid = c.operatorid)
    -> join
    ->  demo.goodsmaster as d on (b.itemnumber = d.itemnumber);
+---------------------+--------------+-----------+----------+-------+------------+
| transdate           | operatorname | goodsname | quantity | price | salesvalue |
+---------------------+--------------+-----------+----------+-------+------------+
| 2020-12-10 00:00:00 | 張靜         | 書(shū)        |    1.000 | 89.00 |      89.00 |
| 2020-12-10 00:00:00 | 張靜         | 筆        |    2.000 |  5.00 |      10.00 |
| 2020-12-11 00:00:00 | 李靜        | 書(shū)        |    2.000 | 89.00 |     178.00 |
| 2020-12-12 00:00:00 | 李靜        | 筆        |   10.000 |  5.00 |      50.00 |
+---------------------+--------------+-----------+----------+-------+------------+
4 rows in set (0.00 sec)

如果我想看看每天的銷(xiāo)售數(shù)量和銷(xiāo)售金額，可以按照一個(gè)字段transdate對(duì)數(shù)據(jù)進(jìn)行分組和統(tǒng)計(jì)。

mysql> select
    -> a.transdate,
    -> sum(b.quantity), -- 統(tǒng)計(jì)分組的總計(jì)銷(xiāo)售數(shù)量
    -> sum(b.salesvalue) -- 統(tǒng)計(jì)分組的總計(jì)銷(xiāo)售金額
    -> from
    ->   demo.transactionhead as a
    -> join
    -> demo.transactiondetails as b on (a.transactionid = b.transactionid)
    -> group by a.transdate;
+---------------------+-----------------+-------------------+
| transdate           | SUM(b.quantity) | SUM(b.salesvalue) |
+---------------------+-----------------+-------------------+
| 2020-12-10 00:00:00 |           3.000 |             99.00 |
| 2020-12-11 00:00:00 |           2.000 |            178.00 |
| 2020-12-12 00:00:00 |          10.000 |             50.00 |
+---------------------+-----------------+-------------------+
3 rows in set (0.00 sec)

如果想看看每天、每個(gè)收銀員的銷(xiāo)售數(shù)量和銷(xiāo)售金額，可以按照2個(gè)字段進(jìn)行分組和統(tǒng)計(jì)，分別是transdate和operatorname:

mysql>  select
    ->     a.transdate,
    ->     c.operatorname,
    ->     sum(b.quantity), -- 數(shù)量求和
    ->     sum(b.salesvalue)-- 金額求和
    -> from
    ->     demo.transactionhead as a
    -> join
    ->     demo.transactiondetails as b on (a.transactionid = b.transactionid)
    -> join
    ->     demo.operator as C on (a.operatorid = c.operatorid)
    -> group by a.transdate , c.operatorname; -- 按照交易日期和操作員分組
+---------------------+--------------+-----------------+-------------------+
| transdate           | operatorname | SUM(b.quantity) | SUM(b.salesvalue) |
+---------------------+--------------+-----------------+-------------------+
| 2020-12-10 00:00:00 | 張靜         |           3.000 |             99.00 |
| 2020-12-11 00:00:00 | 李靜        |           2.000 |            178.00 |
| 2020-12-12 00:00:00 | 李靜        |          10.000 |             50.00 |
+---------------------+--------------+-----------------+-------------------+
3 rows in set (0.00 sec)

可以看到，通過(guò)對(duì)銷(xiāo)售數(shù)據(jù)按照交易日期和收銀員進(jìn)行分組，再對(duì)組內(nèi)數(shù)據(jù)進(jìn)行求和統(tǒng)計(jì)，就實(shí)現(xiàn)了對(duì)每天、每個(gè)收銀員的銷(xiāo)售數(shù)量和銷(xiāo)售金額的查詢(xún)。

好了，知道了group by 的使用方法，我們就來(lái)學(xué)習(xí)having。

回到開(kāi)頭的超市經(jīng)營(yíng)者的需求：查詢(xún)單筆銷(xiāo)售金額超過(guò) 50 元的商品?，F(xiàn)在我們來(lái)使用 having 來(lái)實(shí)現(xiàn)，代碼如下：

mysql> select b.goodsname
    -> from demo.transactiondetails as a
    -> join demo.goodsmaster as b
    -> on (a.itemnumber=b.itemnumber)
    -> group by b.goodsname
    -> having max(a.salesvalue) > 50;
+-----------+
| goodsname |
+-----------+
| 書(shū)        |
+-----------+
1 row in set (0.00 sec)

這種查詢(xún)方式在MySQL里面分四步實(shí)現(xiàn)。

第一步，把流水明細(xì)表和商品信息表通過(guò)公共字段itemnumber連接起來(lái)，從兩個(gè)表中獲取數(shù)據(jù)：

mysql> select
    ->     a.*, b.*
    -> from
    ->     demo.transactiondetails a
    ->         join
    ->     demo.goodsmaster b on (a.itemnumber = b.itemnumber);
+---------------+------------+----------+-------+------------+------------+---------+-----------+---------------+------+------------+
| transactionid | itemnumber | quantity | price | salesvalue | itemnumber | barcode | goodsname | specification | unit | salesprice |
+---------------+------------+----------+-------+------------+------------+---------+-----------+---------------+------+------------+
|             1 |          1 |    1.000 | 89.00 |      89.00 |          1 | 0001    | 書(shū)        | NULL          | 本   |      89.00 |
|             1 |          2 |    2.000 |  5.00 |      10.00 |          2 | 0002    | 筆        | NULL          | 支   |       5.00 |
|             2 |          1 |    2.000 | 89.00 |     178.00 |          1 | 0001    | 書(shū)        | NULL          | 本   |      89.00 |
|             3 |          2 |   10.000 |  5.00 |      50.00 |          2 | 0002    | 筆        | NULL          | 支   |       5.00 |
+---------------+------------+----------+-------+------------+------------+---------+-----------+---------------+------+------------+
4 rows in set (0.00 sec)

查詢(xún)的結(jié)果有點(diǎn)復(fù)雜，為了方便理解，對(duì)結(jié)果進(jìn)行了分類(lèi)，并加了注釋?zhuān)缦聢D所示：

第二步，把結(jié)果集按照商品名稱(chēng)進(jìn)行分組，分組的示意圖如下所示：

組一：

組二：

第三步，對(duì)分組后的數(shù)據(jù)集進(jìn)行篩選，把組中字段salesvalue的最大值 >50 的組篩選出來(lái)，結(jié)果如下：

第四步，返回商品名稱(chēng)。這是我們就得到了結(jié)果：?jiǎn)喂P銷(xiāo)售金額超過(guò)50元的商品。

總結(jié)使用having的查詢(xún)過(guò)程：

首先我們需要把所有的信息都準(zhǔn)備好，包括從關(guān)聯(lián)表中獲取的信息，對(duì)數(shù)據(jù)集進(jìn)行分組，形成一個(gè)包含所有需要的信息的數(shù)據(jù)集合。接著，再通過(guò)having 條件篩選，得到需要的數(shù)據(jù)。

怎么正確的使用where和having？

首先我們需要知道它們的2個(gè)典型區(qū)別：

第一個(gè)區(qū)別是，如果需要通過(guò)連接從關(guān)聯(lián)表中獲取需要的數(shù)據(jù)，where是先篩選后連接，而having是先連接后篩選。

這一點(diǎn)就決定了在關(guān)聯(lián)查詢(xún)中，where比having更高效。因?yàn)閣here可以先篩選，用一個(gè)篩選后的較小的數(shù)據(jù)集和關(guān)聯(lián)表進(jìn)行連接，這樣占用的資源比較少，執(zhí)行效率也就比較高。having則需要先把結(jié)果集準(zhǔn)備好，也就是用未被篩選的數(shù)據(jù)集進(jìn)行關(guān)聯(lián)，然后對(duì)這個(gè)大的數(shù)據(jù)集進(jìn)行篩選，這樣占用的資源就比較多，執(zhí)行效率也比較低。

第二個(gè)區(qū)別是，where可以直接使用表中的字段作為篩選條件，但不能使用分組中的計(jì)算函數(shù)作為篩選條件；having必須要與group by配合使用，可以把分組計(jì)算的函數(shù)和分組字段作為篩選條件。

這也就決定了，在需要對(duì)數(shù)據(jù)進(jìn)行分組統(tǒng)計(jì)的時(shí)候，having可以完成where不能完成的任務(wù)。這是因?yàn)椋诓樵?xún)語(yǔ)法結(jié)構(gòu)中，where在group by 之前，所以無(wú)法對(duì)分組結(jié)果進(jìn)行篩選。having在group by之后，可以使用分組字段和分組中的計(jì)算函數(shù)對(duì)分組的結(jié)果集進(jìn)行篩選，這個(gè)功能是where無(wú)法完成的。

舉個(gè)例子，假如超市經(jīng)營(yíng)者提出，要查詢(xún)是哪個(gè)收銀員、在哪天賣(mài)了2單商品。這種必須先分組才能篩選的查詢(xún)，用where語(yǔ)句實(shí)現(xiàn)就比較難，我們可能要分好幾步，通過(guò)把中間結(jié)果存儲(chǔ)起來(lái)，才能搞定，但是用having，就很輕松，如下：

mysql> select
    ->   a.transdate, c.operatorname
    -> from
    ->   demo.transactionhead as a
    -> join
    ->   demo.transactiondetails as b on (a.transactionid = b.transactionid)
    -> join
    ->   demo.operator as c on (a.operatorid = c.operatorid)
    -> group by a.transdate, c.operatorname
    -> having count(*)=2;  -- 銷(xiāo)售了2單
+---------------------+--------------+
| transdate           | operatorname |
+---------------------+--------------+
| 2020-12-10 00:00:00 | 張 靜        |
+---------------------+--------------+
1 row in set (0.01 sec)

where和having的優(yōu)缺點(diǎn)：

當(dāng)然了，where和having也可以一起配合使用，包含分組統(tǒng)計(jì)函數(shù)的條件用having，普通條件用where。這樣，我們就既利用了where條件的高效快速，又發(fā)揮了having可以使用包含分組統(tǒng)計(jì)函數(shù)的查詢(xún)條件的優(yōu)點(diǎn)，當(dāng)數(shù)據(jù)量特別大的時(shí)候，運(yùn)行效率會(huì)有很大的差別。

您可能感興趣的文章: