本文實例講述了mysql派生表（Derived Table）簡單用法。分享給大家供大家參考，具體如下：
關于這個派生表啊，我們首先得知道，派生表是從select語句返回的虛擬表。派生表類似于臨時表，但是在SELECT語句中使用派生表比臨時表簡單得多，因為它不需要創建臨時表的步驟。所以當SELECT語句的FROM子句中使用獨立子查詢時，我們將其稱為派生表。廢話不多說，我們來具體的解釋：
SELECT
column_list
FROM
* (SELECT
* column_list
* FROM
* table_1) derived_table_name;
WHERE derived_table_name.column > 1...
其中標記星號的地方就使用了派生表。為了詳細點，咱們來看個具體的例子。咱們接下來要從數據庫中的orders表和orderdetails表中獲得2018年銷售收入最高的前5名產品。先來看下表的字段：
咱們先來看下面這條sql：
SELECT
productCode,
ROUND(SUM(quantityOrdered * priceEach)) sales
FROM
orderdetails
INNER JOIN
orders USING (orderNumber)
WHERE
YEAR(shippedDate) = 2018
GROUP BY productCode
ORDER BY sales DESC
LIMIT 5;
這條sql是以兩張表中共有的orderNumber字段為聯合查詢的節點，完事之后，以時間為條件，再以那個什么productCode字段為分組依據，完事獲取分組字段和計算之后的別稱字段，再以sales字段為排序依據，最后提取前五條結果。大概就是這么回事，完事結果集我們可以看做是一張臨時表或者別的什么。大家來看個結果集：
+-------------+--------+
| productCode | sales |
+-------------+--------+
| S18_3232 | 103480 |
| S10_1949 | 67985 |
| S12_1108 | 59852 |
| S12_3891 | 57403 |
| S12_1099 | 56462 |
+-------------+--------+
5 rows in set
完事呢，既然是學習派生表，我們當然可以使用此查詢的結果作為派生表，并將其與products表相關聯。其中，products表的結構如下所示：
mysql> desc products;
+--------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+---------------+------+-----+---------+-------+
| productCode | varchar(15) | NO | PRI | | |
| productName | varchar(70) | NO | | NULL | |
| productLine | varchar(50) | NO | MUL | NULL | |
| productScale | varchar(10) | NO | | NULL | |
| productVendor | varchar(50) | NO | | NULL | |
| productDescription | text | NO | | NULL | |
| quantityInStock | smallint(6) | NO | | NULL | |
| buyPrice | decimal(10,2) | NO | | NULL | |
| MSRP | decimal(10,2) | NO | | NULL | |
+--------------------+---------------+------+-----+---------+-------+
20 rows in set
表結構既然了解完事了，我們就來看下面的sql：
SELECT
productName, sales
FROM
# (SELECT
# productCode,
# ROUND(SUM(quantityOrdered * priceEach)) sales
# FROM
# orderdetails
# INNER JOIN orders USING (orderNumber)
# WHERE
# YEAR(shippedDate) = 2018
# GROUP BY productCode
# ORDER BY sales DESC
# LIMIT 5) top5_products_2018
INNER JOIN
products USING (productCode);
上面#號部分是咱們之前的那條sql，方便大家理解，我使用#標記了出來，大家寫的時候可不能用啊。完事我們來看下這條sql是神馬意思呢？它是把我們用#標記的部分當做一個表，來做一個簡單的聯合查詢而已。然而這個表，我們就叫它派生表，它會在使用過后即時清除的，所以我們在簡化復雜查詢的時候可以考慮使用。廢話不多說，我們來看下結果集：
+-----------------------------+--------+
| productName | sales |
+-----------------------------+--------+
| 1992 Ferrari 360 Spider red | 103480 |
| 1952 Alpine Renault 1300 | 67985 |
| 2001 Ferrari Enzo | 59852 |
| 1969 Ford Falcon | 57403 |
| 1968 Ford Mustang | 56462 |
+-----------------------------+--------+
5 rows in set
然后呢，咱們再來簡單總結下：
- 首先，執行子查詢來創建一個結果集或派生表。
- 然后，在productCode列上使用products表連接top5_products_2018派生表的外部查詢。
完事呢，簡單的派生表的理解和使用就到這里了。咱們再來一個稍稍復雜的來嘗嘗味道哈，首先假設必須將2018年的客戶分為3組：鉑金，白金和白銀。 此外，需要了解每個組中的客戶數量，具體情況如下：
- 訂單總額大于100000的為鉑金客戶；
- 訂單總額為10000至100000的為黃金客戶
- 訂單總額為小于10000的為銀牌客戶
要構建此查詢，首先，我們需要使用case表達式和group by子句將每個客戶放入相應的分組中，如下所示：
SELECT
customerNumber,
ROUND(SUM(quantityOrdered * priceEach)) sales,
(CASE
WHEN SUM(quantityOrdered * priceEach) 10000 THEN 'Silver'
WHEN SUM(quantityOrdered * priceEach) BETWEEN 10000 AND 100000 THEN 'Gold'
WHEN SUM(quantityOrdered * priceEach) > 100000 THEN 'Platinum'
END) customerGroup
FROM
orderdetails
INNER JOIN
orders USING (orderNumber)
WHERE
YEAR(shippedDate) = 2018
GROUP BY customerNumber
ORDER BY sales DESC;
咱們來看下結果集的實例：
+----------------+--------+---------------+
| customerNumber | sales | customerGroup |
+----------------+--------+---------------+
| 141 | 189840 | Platinum |
| 124 | 167783 | Platinum |
| 148 | 150123 | Platinum |
| 151 | 117635 | Platinum |
| 320 | 93565 | Gold |
| 278 | 89876 | Gold |
| 161 | 89419 | Gold |
| ************此處省略了many數據 *********|
| 219 | 4466 | Silver |
| 323 | 2880 | Silver |
| 381 | 2756 | Silver |
+----------------+--------+---------------+
完事嘞，咱們就可以使用上面的查詢所得的表作為派生表來進行關聯查詢并且進行分組，獲取想要的數據了，咱們來看下面的sql感受一下：
SELECT
customerGroup,
COUNT(cg.customerGroup) AS groupCount
FROM
(SELECT
customerNumber,
ROUND(SUM(quantityOrdered * priceEach)) sales,
(CASE
WHEN SUM(quantityOrdered * priceEach) 10000 THEN 'Silver'
WHEN SUM(quantityOrdered * priceEach) BETWEEN 10000 AND 100000 THEN 'Gold'
WHEN SUM(quantityOrdered * priceEach) > 100000 THEN 'Platinum'
END) customerGroup
FROM
orderdetails
INNER JOIN orders USING (orderNumber)
WHERE
YEAR(shippedDate) = 2018
GROUP BY customerNumber) cg
GROUP BY cg.customerGroup;
具體是啥意思，相信聰明如大家肯定比我有更好的理解了，咱就不贅述了。完事來看下結果集：
+---------------+------------+
| customerGroup | groupCount |
+---------------+------------+
| Gold | 61 |
| Platinum | 4 |
| Silver | 8 |
+---------------+------------+
3 rows in set
得嘞，咱就到這里了。
更多關于MySQL相關內容感興趣的讀者可查看本站專題：《MySQL查詢技巧大全》、《MySQL常用函數大匯總》、《MySQL日志操作技巧大全》、《MySQL事務操作技巧匯總》、《MySQL存儲過程技巧大全》及《MySQL數據庫鎖相關技巧匯總》
希望本文所述對大家MySQL數據庫計有所幫助。
您可能感興趣的文章:- 淺談MySQL臨時表與派生表
- Mysql跨表更新 多表update sql語句總結
- mysql鎖表和解鎖語句分享
- mysql 數據表中查找重復記錄
- MySQL中基本的多表連接查詢教程
- MySQL表字段設置默認值(圖文教程及注意細節)
- MySQL中使用表別名與字段別名的基本教程
- mysql查詢表里的重復數據方法
- mysql 導入導出數據庫、數據表的方法
- mysql數據庫修改數據表引擎的方法
- MySql表、字段、庫的字符集修改及查看方法
- 詳解Mysql多表聯合查詢效率分析及優化
