MySQL?where和having的異同

更新時間：2024年02月07日 08:32:12 作者：安然無虞

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

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

一個實際查詢需求

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

我們來分析一下這個需求：需要查詢出一個商品記錄集，限定條件是單筆銷售超過50元。這個時候，我們就需要用到where 和 having了。

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

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

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

同時我們還有一個商品銷售明細表demo.transactiondetails，里面有4條銷售記錄：

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)

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

首先用where關(guā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 |
+-----------+
| 書 |
+-----------+
1 row in set (0.00 sec)

接著，使用having 關(guā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 |
+-----------+
| 書 |
+-----------+
1 row in set (0.00 sec)

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

where

我們先來分析一下剛才使用 where 條件的查詢語句，來看看 MySQL 是如何執(zhí)行這個查詢的。

首先，MySQL 從數(shù)據(jù)表 demo.transactiondetails 中抽取滿足條件“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)

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

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 | 書        |
|             2 |          1 |    2.000 | 89.00 |     178.00 | 書        |
+---------------+------------+----------+-------+------------+-----------+
2 rows in set (0.00 sec)

這個時候，如果查詢商品名稱，就會出現(xiàn)兩個重復(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 |
+-----------+
| 書        |
| 書        |
+-----------+
2 rows in set (0.00 sec)

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

下面我們嘗試一下加上 DISTINCT 關(guā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 |
+-----------+
| 書        |
+-----------+
1 row in set (0.00 sec)

這樣，我們就得到了需要的結(jié)果：單筆銷售金額超過 50 元的商品就是“書”。

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

having

講完了where，我們再說說having 是如何執(zhí)行的。不過，在這之前，我們先來了解一下group by，因為having不能單獨使用，必須要跟group by一起使用。

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

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

假設(shè)現(xiàn)在有一組銷售數(shù)據(jù)，我們需要從里面查詢每天、每個收銀員的銷售數(shù)量和銷售金額。我們通過以下的代碼，來查看一下數(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 | 店長   |
|          2 |        1 | 002    | 李靜        | 13312345678 | 北京    | 110222199501012332 | 收銀員 |
+------------+----------+--------+--------------+-------------+---------+--------------------+--------+
2 rows in set (0.01 sec)

mysql> select
    -> a.transdate,   -- 交易時間
    -> c.operatorname,-- 操作員
    -> d.goodsname,   -- 商品名稱
    -> b.quantity,    -- 銷售數(shù)量
    -> b.price,       -- 價格
    -> 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)
    -> join
    ->  demo.goodsmaster as d on (b.itemnumber = d.itemnumber);
+---------------------+--------------+-----------+----------+-------+------------+
| transdate           | operatorname | goodsname | quantity | price | salesvalue |
+---------------------+--------------+-----------+----------+-------+------------+
| 2020-12-10 00:00:00 | 張靜         | 書        |    1.000 | 89.00 |      89.00 |
| 2020-12-10 00:00:00 | 張靜         | 筆        |    2.000 |  5.00 |      10.00 |
| 2020-12-11 00:00:00 | 李靜        | 書        |    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)

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

mysql> select
    -> a.transdate,
    -> sum(b.quantity), -- 統(tǒng)計分組的總計銷售數(shù)量
    -> sum(b.salesvalue) -- 統(tǒng)計分組的總計銷售金額
    -> 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)

如果想看看每天、每個收銀員的銷售數(shù)量和銷售金額，可以按照2個字段進行分組和統(tǒng)計，分別是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)

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

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

回到開頭的超市經(jīng)營者的需求：查詢單筆銷售金額超過 50 元的商品。現(xiàn)在我們來使用 having 來實現(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 |
+-----------+
| 書        |
+-----------+
1 row in set (0.00 sec)

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

第一步，把流水明細表和商品信息表通過公共字段itemnumber連接起來，從兩個表中獲取數(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    | 書        | 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    | 書        | NULL          | 本   |      89.00 |
|             3 |          2 |   10.000 |  5.00 |      50.00 |          2 | 0002    | 筆        | NULL          | 支   |       5.00 |
+---------------+------------+----------+-------+------------+------------+---------+-----------+---------------+------+------------+
4 rows in set (0.00 sec)

查詢的結(jié)果有點復(fù)雜，為了方便理解，對結(jié)果進行了分類，并加了注釋，如下圖所示：

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

組一：

組二：

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

第四步，返回商品名稱。這是我們就得到了結(jié)果：單筆銷售金額超過50元的商品。

總結(jié)使用having的查詢過程：

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

怎么正確的使用where和having？

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

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

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

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

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

舉個例子，假如超市經(jīng)營者提出，要查詢是哪個收銀員、在哪天賣了2單商品。這種必須先分組才能篩選的查詢，用where語句實現(xiàn)就比較難，我們可能要分好幾步，通過把中間結(jié)果存儲起來，才能搞定，但是用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;  -- 銷售了2單
+---------------------+--------------+
| transdate           | operatorname |
+---------------------+--------------+
| 2020-12-10 00:00:00 | 張 靜        |
+---------------------+--------------+
1 row in set (0.01 sec)

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

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

您可能感興趣的文章: