在 MYSQL 中的一個查詢中執行多個連線

Mehdi Acheli 2023年1月30日 2022年1月13日
  1. 在 MYSQL 中在一個查詢中執行多個連線 - 查詢構造
  2. 在 MYSQL 中的一個查詢中執行多個連線 - 三表連線與自然連線
  3. 在 MYSQL 中的一個查詢中執行多個連線 - 使用 ON 關鍵字的三表連線
  4. 在 MYSQL 中的一個查詢中執行多個連線 - WHERE 塊內的三表連線
  5. 在 MYSQL 中的一個查詢中執行多個連線 - 外部連線案例
在 MYSQL 中的一個查詢中執行多個連線

你有沒有想過如何在 MySQL 的一個查詢中包含多個連線?你來對地方了。請記住,連線允許我們訪問其他表中的資訊。該資訊單獨包含以避免冗餘。讓我們考慮以下示例。讓我們從建立三個表開始。

  • client(client_id, client_name) 定義了一個由 client_id 標識並命名為 client_name 的客戶。
CREATE TABLE client (
    client_id INT PRIMARY KEY,
    client_name VARCHAR(255)
);
  • product(product_id, product_name, unit_price, supplier_cost) 代表商店中由 product_id 標識並命名為 product_nameunit_price 出售的產品。從供應商處購買一單位產品的成本由 supplier_cost 給出:
CREATE TABLE product (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(255),
    unit_price INT,
    supplier_cost INT
);
  • product_order(order_id, product_id, client_id, quantity) 表示由 order_id 標識的訂單,引用客戶 client_id 購買的產品 product_id,數量為 quantity
CREATE TABLE product_order (
    order_id INT PRIMARY KEY,
    product_id INT NOT NULL,
    client_id INT NOT NULL,
    quantity INT NOT NULL,
    FOREIGN KEY (product_id) REFERENCES product(product_id),
    FOREIGN KEY (client_id) REFERENCES client(client_id)
);

如你所見,它非常簡約,但它完全可以勝任。花點時間注意沒有多餘的資訊。產品名稱不在 product_order 表中。如果是這樣的話,每次購買時都會重複出現該產品的名稱。

我們在這裡的工作是返還為每個客戶實現的利潤。從業務的角度來看,可以提出更復雜和有用的查詢,但我們只是展示了多表連線。你可以使用以下值填充資料庫以測試查詢。

INSERT INTO client VALUES (1, 'John');
INSERT INTO client VALUES (2, 'Mehdi');
INSERT INTO client VALUES (3, 'Ali');
INSERT INTO product VALUES (1, 'laptop', 500, 250);
INSERT INTO product VALUES (2, 'tablet', 600, 550);
INSERT INTO product_order VALUES (1, 1, 1, 3);
INSERT INTO product_order VALUES (2, 1, 1, 3);
INSERT INTO product_order VALUES (3, 2, 2, 6);

在 MYSQL 中在一個查詢中執行多個連線 - 查詢構造

與訂單相關的利潤按以下方式計算:

$$profit = quantity * (unit\_price - supplier\_cost)$$

如你所見,對於我們的目標查詢,我們需要三個值。在 product_order 中找到數量,在 product 中找到單價和供應商成本,最後,在 client 中找到客戶名稱。因此,需要三表連線。我們在每次查詢後給出查詢結果。

在 MYSQL 中的一個查詢中執行多個連線 - 三表連線與自然連線

根據設計,不同表中的外來鍵與引用的主鍵具有相同的名稱。我們可以通過以下方式使用自然連線來連結三個表。

SELECT client_name, SUM(quantity * (unit_price - supplier_cost)) AS profit
FROM product_order 
     NATURAL JOIN product
     NATURAL JOIN client
GROUP BY client_id;

輸出:

| client_name | profit |
| ----------- | ------ |
| John        | 1500   |
| Mehdi       | 300    |

在 MYSQL 中的一個查詢中執行多個連線 - 使用 ON 關鍵字的三表連線

還有另一種可能來實現我們的目標。我們可以使用 ON 關鍵字,如下所示:

SELECT client_name, SUM(product_order.quantity * (product.unit_price - product.supplier_cost)) AS profit
FROM product_order 
     JOIN product
        ON product_order.product_id = product.product_id 
     JOIN client
        ON product_order.client_id = client.client_id
GROUP BY client.client_id;

輸出:

| client_name | profit |
| ----------- | ------ |
| John        | 1500   |
| Mehdi       | 300    |

在 MYSQL 中的一個查詢中執行多個連線 - WHERE 塊內的三表連線

最後,執行連線的條件可以合併到 WHERE 塊本身中。

SELECT client_name, SUM(product_order.quantity * (product.unit_price - product.supplier_cost)) AS profit
FROM product_order 
     JOIN product
     JOIN client
WHERE product_order.product_id = product.product_id 
      AND product_order.client_id = client.client_id
GROUP BY client.client_id;

輸出:

| client_name | profit |
| ----------- | ------ |
| John        | 1500   |
| Mehdi       | 300    |

在 MYSQL 中的一個查詢中執行多個連線 - 外部連線案例

回想一下,連線是在屬性之間相等的條件下執行的。如果表的某些行中不存在這樣的相等性,則合併的行將不會包含在結果連線中(稱為內部連線,這是預設的連線)。這可能有問題。特別是,對於上述查詢,存在於資料庫中但從未購買過任何產品的客戶不會出現在結果中。那是因為它們在 product_order 表中沒有關聯的行,如下圖所示。當使用 Web 應用程式時,某些客戶建立了帳戶但尚未購買任何東西時,可能會出現這種情況。一種解決方案是使用 LEFT OUTER JOIN,其中沒有先前訂單的客戶與 NULL product_order 屬性相關聯。最後的查詢是:

SELECT client_name, SUM(IFNULL(quantity, 0) * (IFNULL(unit_price, 0) - IFNULL(supplier_cost, 0))) AS profit
FROM client
     LEFT OUTER JOIN product_order
         ON product_order.client_id = client.client_id
     LEFT OUTER JOIN product
         ON product.product_id = product_order.product_id
GROUP BY client.client_id;

輸出:

| client_name | profit |
| ----------- | ------ |
| John        | 1500   |
| Mehdi       | 300    |
| Ali         | 0      |

如上所述,如果當前客戶沒有訂單,product_order 表屬性設定為 NULL,包括數量 - product 表屬性相同。如果我們希望這些客戶的利潤值為零,我們可以使用 IFNULL 函式將 NULL 數量值轉換為零。unit_pricesupply_cost 相同。可以使用除 0 之外的任何其他預設值。有關 IFNULL 函式的詳細資訊,請參閱 https://www.w3schools.com/sql/func_mysql_ifnull.asp

我們在下圖中給出了內部連線與外部連線的比較的說明。

MySQL 中的多連線

相關文章 - MySQL Join