Skip to main content

MySQL派生表的基本指南

MySQL派生表简介

派生表是从SELECT语句返回的虚拟表。派生表类似于临时表,但在SELECT语句中使用派生表比临时表简单得多,因为它不需要创建临时表。

派生表和子查询这一术语经常互换使用。在SELECT语句的FROM子句中使用独立子查询时,它也称为派生表。

下面演示了使用派生表的查询:

MySQL Derived Table

请注意,独立子查询是可以独立于外部查询执行的子查询。

与子查询不同,派生表必须有别名,以便以后在查询中引用其名称。如果派生表没有别名,MySQL将发出以下错误:

Every derived table must have its own alias.

以下说明了使用派生表的查询的语法:

SELECT 
select_list
FROM
(SELECT
select_list
FROM
table_1) derived_table_name
WHERE
derived_table_name.c1 > 0;

一个简单的MySQL派生表示例

以下查询从示例数据库中的orders和orderdetails表中获取2003年按销售收入分列的前五名产品:

Orders and OrderDetails Tables

SELECT 
productCode,
ROUND(SUM(quantityOrdered * priceEach)) sales
FROM
orderdetails
INNER JOIN
orders USING (orderNumber)
WHERE
YEAR(shippedDate) = 2003
GROUP BY productCode
ORDER BY sales DESC
LIMIT 5;

MySQL Derived Table Example 1

您可以将此查询的结果用作派生表,并将其与products表联接,如下所示:

products table

SELECT 
productName, sales
FROM
(SELECT
productCode,
ROUND(SUM(quantityOrdered * priceEach)) sales
FROM
orderdetails
INNER JOIN orders USING (orderNumber)
WHERE
YEAR(shippedDate) = 2003
GROUP BY productCode
ORDER BY sales DESC
LIMIT 5) top5products2003
INNER JOIN
products USING (productCode);

下面显示了上述查询的输出:

MySQL Derived Table - Top 5 Products 2013

在本例中:

一个更复杂的MySQL派生表示例

假设您必须将2003年购买产品的客户分为3组:白金、黄金和白银。你需要知道在以下情况下每组的客户数量:

要形成此查询,首先需要使用CASE表达式和groupby子句将每个客户放入各自的组中,如下所示:

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) = 2003
GROUP BY customerNumber;

以下是查询的输出:

MySQL Derived Table - Customer Groups

然后,您可以使用此查询作为派生表,并按如下方式执行分组:

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) = 2003
GROUP BY customerNumber) cg
GROUP BY cg.customerGroup;

查询返回客户组和每个组中的客户数。

MySQL Derived Table - Customer Group Counts

在本教程中,您已经学习了如何使用MySQL派生表来简化复杂的查询,这些表是FROM子句中的子查询。