【Mysql】教程全解（四）表連接

L羅樂 2018-05-06

展開全文

4.1 別名

有時，列的名稱是一些表達(dá)式，使查詢的輸出很難理解。要給列一個描述性名稱，可以使用列別名。

以下語句說明了如何使用列別名：

SELECT 
 [column_1 | expression] AS descriptive_name
FROM table_name;

SQL

要給列添加別名，可以使用AS關(guān)鍵詞后跟別名。如果別名包含空格，則必須引用以下內(nèi)容：

SELECT 
 [column_1 | expression] AS `descriptive name`
FROM table_name;

SQL

因為AS關(guān)鍵字是可選的，可以在語句中省略它。請注意，還可以在表達(dá)式上使用別名。

我們來看看示例數(shù)據(jù)庫(yiibaidb)中的employees表，其表結(jié)構(gòu)如下所示 -

以下查詢選擇員工的名字和姓氏，并將其組合起來生成全名。 CONCAT_WS函數(shù)用于連接名字和姓氏。

SELECT 
    CONCAT_WS(', ', lastName, firstname)
FROM
    employees;

SQL

執(zhí)行上面代碼，得到以下結(jié)果 -

mysql> SELECT 
    CONCAT_WS(', ', lastName, firstname)
FROM
    employees;
 -------------------------------------- 
| CONCAT_WS(', ', lastName, firstname) |
 -------------------------------------- 
| Murphy, Diane                        |
| Patterson, Mary                      |
| Firrelli, Jeff                       |
| Patterson, William                   |
| Bondur, Gerard                       |
| Bow, Anthony                         |
| Jennings, Leslie                     |
| Thompson, Leslie                     |
| Firrelli, Julie                      |
| Patterson, Steve                     |
| Tseng, Foon Yue                      |
| Vanauf, George                       |
| Bondur, Loui                         |
| Hernandez, Gerard                    |
| Castillo, Pamela                     |
| Bott, Larry                          |
| Jones, Barry                         |
| Fixter, Andy                         |
| Marsh, Peter                         |
| King, Tom                            |
| Nishi, Mami                          |
| Kato, Yoshimi                        |
| Gerard, Martin                       |
 -------------------------------------- 
23 rows in set

Shell

在上面示例中，列標(biāo)題很難閱讀理解?？梢詾檩敵龅臉?biāo)題分配一個有意義的列別名，以使其更可讀，如以下查詢：

SELECT
 CONCAT_WS(', ', lastName, firstname) AS `Full name`
FROM
 employees;

SQL

執(zhí)行上面代碼，得到以下結(jié)果 -

mysql> SELECT
 CONCAT_WS(', ', lastName, firstname) AS `Full name`
FROM
 employees;
 -------------------- 
| Full name          |
 -------------------- 
| Murphy, Diane      |
| Patterson, Mary    |
| Firrelli, Jeff     |
... ...
| King, Tom          |
| Nishi, Mami        |
| Kato, Yoshimi      |
| Gerard, Martin     |
 -------------------- 
23 rows in set

Shell

在MySQL中，可以使用ORDER BY，GROUP BY和HAVING子句中的列別名來引用該列。

以下查詢使用ORDER BY子句中的列別名按字母順序排列員工的全名：

SELECT
 CONCAT_WS(' ', lastName, firstname) `Full name`
FROM
 employees
RDER BY
 `Full name`;

SQL

執(zhí)行上面代碼，得到以下結(jié)果 -

mysql> SELECT
 CONCAT_WS(' ', lastName, firstname) `Full name`
FROM
 employees
ORDER BY
 `Full name`;
 ------------------- 
| Full name         |
 ------------------- 
| Bondur Gerard     |
| Bondur Loui       |
| Bott Larry        |
| Bow Anthony       |
| Castillo Pamela   |
| Firrelli Jeff     |
| Firrelli Julie    |
| Fixter Andy       |
| Gerard Martin     |
| Hernandez Gerard  |
| Jennings Leslie   |
| Jones Barry       |
| Kato Yoshimi      |
| King Tom          |
| Marsh Peter       |
| Murphy Diane      |
| Nishi Mami        |
| Patterson Mary    |
| Patterson Steve   |
| Patterson William |
| Thompson Leslie   |
| Tseng Foon Yue    |
| Vanauf George     |
 ------------------- 
23 rows in set

Shell

以下語句查詢總金額大于60000的訂單。它在GROUP BY和HAVING子句中使用列別名。

SELECT
 orderNumber `Order no.`,
 SUM(priceEach * quantityOrdered) total
FROM
 orderdetails
GROUP BY
 `Order no.`
HAVING
 total > 60000;

SQL

執(zhí)行上面查詢語句，得到以下結(jié)果 -

請注意，不能在WHERE子句中使用列別名。原因是當(dāng)MySQL評估求值WHERE子句時，SELECT子句中指定的列的值可能尚未確定。

MySQL表的別名

可以使用別名為表添加不同的名稱。使用AS關(guān)鍵字在表名稱分配別名，如下查詢語句語法：

table_name AS table_alias

SQL

該表的別名稱為表別名。像列別名一樣，AS關(guān)鍵字是可選的，所以完全可以省略它。

一般在包含INNER JOIN，LEFT JOIN，self join子句和子查詢的語句中使用表別名。

下面來看看客戶(customers)和訂單(orders)表，它們的ER圖如下所示 -

兩個表都具有相同的列名稱：customerNumber。如果不使用表別名來指定是哪個表中的customerNumber列，則執(zhí)行查詢時將收到類似以下錯誤消息：

Error Code: 1052. Column 'customerNumber' in on clause is ambiguous

Shell

為避免此錯誤，應(yīng)該使用表別名來限定customerNumber列：

SELECT
 customerName,
 COUNT(o.orderNumber) total
FROM
 customers c
INNER JOIN orders o ON c.customerNumber = o.customerNumber
GROUP BY customerName
HAVING total >=5
ORDER BY total DESC;

SQL

執(zhí)行上面查詢語句，得到以下結(jié)果 -

上面的查詢從客戶(customers)和訂單(orders)表中選擇客戶名稱和訂單數(shù)量。它使用c作為customers表的表別名，o作為orders表的表別名。customers和orders表中的列通過表別名(c和o)引用。

如果您不在上述查詢中使用別名，則必須使用表名稱來引用其列，這樣的會使得查詢?nèi)唛L且可讀性較低，如下 -

SELECT
       customers.customerName,
 COUNT(orders.orderNumber) total
FROM  customers
INNER JOIN orders ON customers.customerNumber = orders.customerNumber
GROUP BY  customerName
ORDER BY  total DESC

4.2 INNER JOIN

MySQL INNER JOIN子句將一個表中的行與其他表中的行進(jìn)行匹配，并允許從兩個表中查詢包含列的行記錄。

INNER JOIN子句是SELECT語句的可選部分，它出現(xiàn)在FROM子句之后。

在使用INNER JOIN子句之前，必須指定以下條件：

首先，在FROM子句中指定主表。
其次，表中要連接的主表應(yīng)該出現(xiàn)在INNER JOIN子句中。理論上說，可以連接多個其他表。但是，為了獲得更好的性能，應(yīng)該限制要連接的表的數(shù)量(最好不要超過三個表)。
第三，連接條件或連接謂詞。連接條件出現(xiàn)在INNER JOIN子句的ON關(guān)鍵字之后。連接條件是將主表中的行與其他表中的行進(jìn)行匹配的規(guī)則。

INNER JOIN子句的語法如下：

SELECT column_list
FROM t1
INNER JOIN t2 ON join_condition1
INNER JOIN t3 ON join_condition2
...
WHERE where_conditions;

SQL

假設(shè)使用INNER JOIN子句連接兩個表：t1和t2，我們來簡化上面的語法。

SELECT column_list
FROM t1
INNER JOIN t2 ON join_condition;

SQL

對于t1表中的每一行，INNER JOIN子句將它與t2表的每一行進(jìn)行比較，以檢查它們是否都滿足連接條件。當(dāng)滿足連接條件時，INNER JOIN將返回由t1和t2表中的列組成的新行。

請注意，t1和t2表中的行必須根據(jù)連接條件進(jìn)行匹配。如果找不到匹配項，查詢將返回一個空結(jié)果集。當(dāng)連接超過2個表時，也應(yīng)用此邏輯。

以下維恩圖說明了INNER JOIN子句的工作原理。結(jié)果集中的行必須出現(xiàn)在兩個表中：t1和t2，如兩個圓的交叉部分所示 -

在MySQL INNER JOIN中避免列錯誤

如果連接具有相同列名稱的多個表，則必須使用表限定符引用SELECT和ON子句的列，以避免列錯誤。

例如，如果t1和t2表都具有名為c的一個相同列名，則必須在SELECT和ON子句中使用表限定符，如使用t1.c或t2.c指定引用是那個表中的c列。

為了節(jié)省書寫表限定符的時間，可以在查詢中使用表別名。例如，可以長名稱verylonglonglong_tablename表使用表別名，并使用t.column引用其列，而不是使用verylonglonglong_tablename.column，但是如果喜歡書寫或使用這么長的表名稱，那么也應(yīng)該允許你的開發(fā)伙伴罵你幾句類似：傻逼~等這樣的話！

MySQL INNER JOIN示例

下面來看看示例數(shù)據(jù)庫(yiibaidb)中的產(chǎn)品(products)和產(chǎn)品線(productlines)表。它們的 ER 圖如下所示 -

在上面圖中，products表中的productLine列參考引用productlines表的productline列。 products表中的productLine列稱為外鍵列。

通常，連接具有外鍵關(guān)系的表，如產(chǎn)品線(productlines)和產(chǎn)品(products)表?，F(xiàn)在，如果想獲取以下數(shù)據(jù) -

獲取products表中的productCode和productName列的值。
獲取productlines表產(chǎn)品線的描述 - textDescription列的值。

為此，需要通過使用INNER JOIN子句根據(jù)productline列匹配行來從兩個表中查詢選擇數(shù)據(jù)，如下所示：

SELECT 
    productCode, 
    productName, 
    textDescription
FROM    products t1        
INNER JOIN  productlines t2 ON t1.productline = t2.productline;

SQL

執(zhí)行上面查詢，得到下面的結(jié)果(部分)-

由于兩個表的連接列是使用相同一個列：productline，因此可以使用以下語法：

SELECT 
    productCode, 
    productName, 
    textDescription
FROM
    products        
INNER JOIN
    productlines USING (productline);

SQL

上面語句返回相同的結(jié)果集，但是使用此語法，不必使用表的別名。

MySQL INNER JOIN GROUP BY子句

請參閱以下訂單和訂單詳細(xì)表，orders表和orderdetails表的結(jié)構(gòu)如下所示 -

可以使用具有GROUP BY子句的INNER JOIN子句從orders和orderdetails表中獲取訂單號，訂單狀態(tài)和總銷售額，如下所示：

SELECT 
    T1.orderNumber,
    status,
    SUM(quantityOrdered * priceEach) total
FROM
    orders AS T1        
INNER JOIN
    orderdetails AS T2 ON T1.orderNumber = T2.orderNumber
GROUP BY orderNumber;

SQL

執(zhí)行上面查詢，結(jié)果如下所示(部分) -

類似地，以下語句查詢與上述得到結(jié)果相同：

SELECT 
    orderNumber,
    status,
    SUM(quantityOrdered * priceEach) total
FROM
    orders        
INNER JOIN
    orderdetails USING (orderNumber)
GROUP BY orderNumber;

SQL

MySQL INNER JOIN使用等于以外的運(yùn)算符

到目前為止，您已經(jīng)看到連接謂詞使用相等的運(yùn)算符(=)來匹配行。但是也可以使用大于(>)，小于(<)和不等于(<>)運(yùn)算符的其他運(yùn)算符來形成連接謂詞。

以下查詢使用少于(<)連接來查找低于代碼為S10_1678的產(chǎn)品的銷售價格的制造商建議零售價(MSRP)的所有產(chǎn)品。

SELECT 
    orderNumber, 
    productName, 
    msrp, 
    priceEach
FROM
    products p        
INNER JOIN
    orderdetails o ON p.productcode = o.productcode AND p.msrp > o.priceEach
WHERE
    p.productcode = 'S10_1678';

SQL

執(zhí)行上面查詢語句，得到以下輸出結(jié)果 -

mysql> SELECT 
    orderNumber, 
    productName, 
    msrp, 
    priceEach
FROM
    products p
        INNER JOIN
    orderdetails o ON p.productcode = o.productcode
        AND p.msrp > o.priceEach
WHERE
    p.productcode = 'S10_1678';
 ------------- --------------------------------------- ------ ----------- 
| orderNumber | productName                           | msrp | priceEach |
 ------------- --------------------------------------- ------ ----------- 
|       10107 | 1969 Harley Davidson Ultimate Chopper | 95.7 | 81.35     |
|       10121 | 1969 Harley Davidson Ultimate Chopper | 95.7 | 86.13     |
|       10134 | 1969 Harley Davidson Ultimate Chopper | 95.7 | 90.92     |
|       10145 | 1969 Harley Davidson Ultimate Chopper | 95.7 | 76.56     |
|       10159 | 1969 Harley Davidson Ultimate Chopper | 95.7 | 81.35     |
|       10168 | 1969 Harley Davidson Ultimate Chopper | 95.7 | 94.74     |
|       10399 | 1969 Harley Davidson Ultimate Chopper | 95.7 | 77.52     |
|       10403 | 1969 Harley Davidson Ultimate Chopper | 95.7 | 85.17     |
... ...
|       10417 | 1969 Harley Davidson Ultimate Chopper | 95.7 | 79.43     |
 ------------- --------------------------------------- ------ ----------- 
26 rows in set

4.3 LEFT JOIN

MySQL LEFT JOIN子句允許您從兩個或多個數(shù)據(jù)庫表查詢數(shù)據(jù)。LEFT JOIN子句是SELECT語句的可選部分，出現(xiàn)在FROM子句之后。

我們假設(shè)要從兩個表t1和t2查詢數(shù)據(jù)。以下語句說明了連接兩個表的LEFT JOIN子句的語法：

SELECT 
    t1.c1, t1.c2, t2.c1, t2.c2
FROM
    t1        
LEFT JOIN
    t2 ON t1.c1 = t2.c1;

SQL

當(dāng)使用LEFT JOIN子句將t1表加入t2表時，如果來自左表t1的行與基于連接條件(t1.c1 = t2.c1)的右表t2匹配，則該行將被包含在結(jié)果集中。

如果左表中的行與右表中的行不匹配，則還將選擇左表中的行并與右表中的“假”行組合?！?em>假”行對于SELECT子句中的所有相應(yīng)列都包含NULL值。

換句話說，LEFT JOIN子句允許您從匹配的左右表中查詢選擇行記錄，連接左表(t1)中的所有行，即使在右表(t2)中找不到匹配的行也顯示出來，但使用NULL值代替。

下圖可幫助您可視化LEFT JOIN子句的工作原理。兩個圓圈之間的交點(diǎn)是兩個表中匹配的行，左圓的剩余部分(白色部分)是t1表中不存在t2表中任何匹配行的行。因此，左表中的所有行都包含在結(jié)果集中。

請注意，如果這些子句在查詢中可用，返回的行也必須與WHERE和HAVING子句中的條件相匹配。

2. MySQL LEFT JOIN示例

2.1 使用MySQL LEFT JOIN子句來連接兩個表

我們來看看在示例數(shù)據(jù)庫(yiibaidb)中的兩個表：訂單表和客戶表，兩個表的 ER 圖如下所示 -

在上面的數(shù)據(jù)庫圖中：

訂單(orders)表中的每個訂單必須屬于客戶(customers)表中的客戶。
客戶(customers)表中的每個客戶在訂單(orders)表中可以有零個或多個訂單。

要查詢每個客戶的所有訂單，可以使用LEFT JOIN子句，如下所示：

SELECT
 c.customerNumber,
 c.customerName,
 orderNumber,
 o.status
FROM
 customers c
LEFT JOIN orders o ON c.customerNumber = o.customerNumber;

SQL

執(zhí)行上面查詢語句，得到以下結(jié)果(部分) -

mysql> SELECT
 c.customerNumber,
 c.customerName,
 orderNumber,
 o.status
FROM
 customers c
LEFT JOIN orders o ON c.customerNumber = o.customerNumber;
 ---------------- ------------------------------------ ------------- ------------ 
| customerNumber | customerName                       | orderNumber | status     |
 ---------------- ------------------------------------ ------------- ------------ 
|            103 | Atelier graphique                  |       10123 | Shipped    |
|            103 | Atelier graphique                  |       10298 | Shipped   |
... 省略部分 ...
|            477 | Mit Vergngen & Co.                 | NULL        | NULL       |
|            480 | Kremlin Collectables, Co.          | NULL        | NULL       |
|            481 | Raanan Stores, Inc                 | NULL        | NULL       |
|            484 | Iberia Gift Imports, Corp.         |       10184 | Shipped    |
|            484 | Iberia Gift Imports, Corp.         |       10303 | Shipped    |
|            486 | Motor Mint Distributors Inc.       |       10109 | Shipped    |
|            486 | Motor Mint Distributors Inc.       |       10236 | Shipped  |
 ---------------- ------------------------------------ ------------- ------------ 
350 rows in set

Shell

左表是customers表，因此，所有客戶都包含在結(jié)果集中。但是，結(jié)果集中有一些行具有客戶數(shù)據(jù)，但沒有訂單數(shù)據(jù)。如：customerNumber列值為：477，480等。這些行中的訂單數(shù)據(jù)為NULL。也就是說這些客戶在orders表中沒有任何訂單(未購買過任何產(chǎn)品)。

因為我們使用相同的列名(orderNumber)來連接兩個表，所以可以使用以下語法使查詢更短：

SELECT
 c.customerNumber,
 customerName,
 orderNumber,
 status
FROM
 customers c
LEFT JOIN orders USING (customerNumber);

SQL

在上面查詢語句中，下面的子句 -

USING (customerNumber)

SQL

相當(dāng)于 -

ON c.customerNumber = o.customerNumber

SQL

如果使用INNER JOIN子句替換LEFT JOIN子句，則只能獲得至少有下過一個訂單的客戶。

2.2 使用MySQL LEFT JOIN子句來查找不匹配的行

當(dāng)您想要找到右表中與不匹配的左表中的行時，LEFT JOIN子句非常有用。要查詢兩個表之間的不匹配行，可以向SELECT語句添加一個WHERE子句，以僅查詢右表中的列值包含NULL值的行。

例如，要查找沒有下過訂單的所有客戶，請使用以下查詢：

SELECT 
    c.customerNumber, 
    c.customerName, 
    orderNumber, 
    o.status
FROM
    customers c
        LEFT JOIN
    orders o ON c.customerNumber = o.customerNumber
WHERE
    orderNumber IS NULL;

SQL

執(zhí)行上面查詢語句，得到以下結(jié)果 -

mysql> SELECT 
    c.customerNumber, 
    c.customerName, 
    orderNumber, 
    o.status
FROM
    customers c
        LEFT JOIN
    orders o ON c.customerNumber = o.customerNumber
WHERE
    orderNumber IS NULL;

3. WHERE子句與ON子句中的條件

請參見以下示例。

SELECT 
    o.orderNumber, 
    customerNumber, 
    productCode
FROM
    orders o        
LEFT JOIN
    orderDetails USING (orderNumber)
WHERE
    orderNumber = 10123;

SQL

在本示例中，我們使用LEFT JOIN子句來查詢orders表和orderDetails表中的數(shù)據(jù)。該查詢返回訂單號為10123的訂單及其購買產(chǎn)品明細(xì)信息(如果有的話)。

但是，如果將條件從WHERE子句移動到ON子句：

SELECT 
    o.orderNumber, 
    customerNumber, 
    productCodeFROM
    orders o        LEFT JOIN
    orderDetails d ON o.orderNumber = d.orderNumber       
                  AND o.orderNumber = 10123;

SQL

想想上面代碼將會輸出什么結(jié)果 -

4.4 CROSS JOIN （笛卡爾積）

CROSS JOIN子句從連接的表返回行的笛卡兒乘積。

假設(shè)使用CROSS JOIN連接兩個表。結(jié)果集將包括兩個表中的所有行，其中結(jié)果集中的每一行都是第一個表中的行與第二個表中的行的組合。當(dāng)連接的表之間沒有關(guān)系時，會使用這種情況。

要特別注意的是，如果每個表有1000行，那么結(jié)果集中就有1000 x 1000 = 1,000,000行，那么數(shù)據(jù)量是非常巨大的。

下面說明連接兩個表：T1和T2的CROSS JOIN子句的語法：

SELECT 
    *
FROM
    T1        
CROSS JOIN
    T2;

SQL

請注意，與INNER JOIN或LEFT JOIN子句不同，CROSS JOIN子句不具有連接條件。

如果添加了WHERE子句，如果T1和T2有關(guān)系，則CROSS JOIN的工作方式與INNER JOIN子句類似，如以下查詢所示：

SELECT 
    *
FROM
    T1        
CROSS JOIN
    T2
WHERE
    T1.id = T2.id;

SQL

MySQL CROSS JOIN子句示例

下面我們將使用以下幾個表來演示CROSS JOIN的工作原理。

CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;

DROP TABLE IF EXISTS products;

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100),
    price DECIMAL(13 , 2 ));

DROP TABLE IF EXISTS sales;

CREATE TABLE stores (
    id INT PRIMARY KEY AUTO_INCREMENT,
    store_name VARCHAR(100));

DROP TABLE IF EXISTS sales;

CREATE TABLE sales (
    product_id INT,
    store_id INT,
    quantity DECIMAL(13 , 2 ) NOT NULL,
    sales_date DATE NOT NULL,
    PRIMARY KEY (product_id , store_id),
    FOREIGN KEY (product_id)
        REFERENCES products (id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (store_id)
        REFERENCES stores (id)
        ON DELETE CASCADE ON UPDATE CASCADE);

SQL

上面語句中，創(chuàng)建了三個表：

產(chǎn)品(products)表包含產(chǎn)品編號，產(chǎn)品名稱和銷售價格等產(chǎn)品主要數(shù)據(jù)。
商店(stores)表包含銷售產(chǎn)品的商店信息。
銷售(sales)表包含在特定商店按數(shù)量和日期銷售的產(chǎn)品。

假設(shè)有三個產(chǎn)品：iPhone，iPad和Macbook Pro，在北部(North)和南部(South)的這兩個商店中出售。

INSERT INTO products(product_name, price)
            VALUES('iPhone', 699),
                  ('iPad',599),
                  ('Macbook Pro',1299);
INSERT INTO stores(store_name)
VALUES('North'),
      ('South');
INSERT INTO sales(store_id,product_id,quantity,sales_date)
VALUES(1,1,20,'2017-01-02'),
      (1,2,15,'2017-01-05'),
      (1,3,25,'2017-01-05'),
      (2,1,30,'2017-01-02'),
      (2,2,35,'2017-01-05');

SQL

要獲得每個商店和每個產(chǎn)品的總銷售額，您可以計算銷售額，并按商店和產(chǎn)品分組如下：

SELECT 
    store_name,
    product_name,
    SUM(quantity * price) AS revenue
FROM
    sales        
INNER JOIN
    products ON products.id = sales.product_id        
INNER JOIN
    stores ON stores.id = sales.store_id
GROUP BY store_name , product_name;

SQL

執(zhí)行上面查詢，得到以下結(jié)果 -

mysql> SELECT 
    store_name,
    product_name,
    SUM(quantity * price) AS revenue
FROM
    sales
        INNER JOIN
    products ON products.id = sales.product_id
        INNER JOIN
    stores ON stores.id = sales.store_id
GROUP BY store_name , product_name; 
 ------------ -------------- ------------ 
| store_name | product_name | revenue    |
 ------------ -------------- ------------ 
| North      | iPad         | 8985.0000  |
| North      | iPhone       | 13980.0000 |
| North      | Macbook Pro  | 32475.0000 |
| South      | iPad         | 20965.0000 |
| South      | iPhone       | 20970.0000 |
 ------------ -------------- ------------ 
5 rows in set

Shell

現(xiàn)在，如果你想知道哪個商店中的哪些產(chǎn)品的沒有銷售怎么辦？上面的查詢無法回答這個問題。

要解決這個問題，可以使用CROSS JOIN子句。

首先，使用CROSS JOIN子句來獲取所有商店和產(chǎn)品的組合：

SELECT 
    store_name, product_name
FROM
    stores AS a
        CROSS JOIN
    products AS b;

SQL

執(zhí)行上面查詢語句，得到以下結(jié)果 -

mysql> SELECT 
    store_name, product_name
FROM
    stores AS a
        CROSS JOIN
    products AS b;
 ------------ -------------- 
| store_name | product_name |
 ------------ -------------- 
| North      | iPhone       |
| South      | iPhone       |
| North      | iPad         |
| South      | iPad         |
| North      | Macbook Pro  |
| South      | Macbook Pro  |
 ------------ -------------- 
6 rows in set

Shell

接下來，將上述查詢的結(jié)果與按商店和產(chǎn)品返回總銷售額的查詢相結(jié)合。以下查詢說明了這個想法：

SELECT 
    b.store_name,
    a.product_name,
    IFNULL(c.revenue, 0) AS revenue
FROM
         products AS a
    CROSS JOIN
         stores AS b
        LEFT JOIN
    (SELECT 
        stores.id AS store_id,
        products.id AS product_id,
        store_name,
            product_name,
            ROUND(SUM(quantity * price), 0) AS revenue    
FROM
        sales    
       INNER JOIN products ON products.id = sales.product_id    
       INNER JOIN stores ON stores.id = sales.store_id    
GROUP BY store_name , product_name) AS c ON c.store_id = b.id       
 AND c.product_id= a.idORDER BY b.store_name;

SQL

請注意，如果收入為NULL(表示商店沒有銷售的產(chǎn)品)，則查詢使用IFNULL函數(shù)返回0。

通過這樣使用CROSS JOIN子句，可以解決類似這樣的問題，例如銷售人員按月查找銷售收入，即使推銷員在特定月份沒有銷售產(chǎn)品。

本站是提供個人知識管理的網(wǎng)絡(luò)存儲空間，所有內(nèi)容均由用戶發(fā)布，不代表本站觀點(diǎn)。請注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購買等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請點(diǎn)擊一鍵舉報。

轉(zhuǎn)藏 分享

QQ空間 QQ好友新浪微博微信

獻(xiàn)花（0） +1

來自： L羅樂 > 《SQL》

舉報/認(rèn)領(lǐng)