mysql count 派生表_MySQL 派生表

MySQL 派生表

简介：在本教程中，您将了解MySQL派生表以及如何简化复杂查询。

MySQL派生表简介

派生表是从临时表，但在SELECT语句中使用派生表比临时表简单得多，因为它不需要创建临时表的步骤。

术语派生表和子查询通常可互换使用。当在SELECT语句的FROM子句中使用独立子查询时，我们将其称为派生表。

以下说明了使用派生表的查询：

注意：独立子查询是一个子查询，它可以独立于包含它的语句执行。

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

Every derived table must have its own alias.

以下说明了使用派生表的SQL语句：

SELECT

column_list

FROM

(SELECT

column_list

FROM

table_1) derived_table_name;

WHERE derived_table_name.c1 > 0;

一个简单的MySQL派生表实例

以下查询从示例数据库中的orders和orderdetails表中获取2003年销售收入的前5个产品：

+----------------+

| orders |

+----------------+

| orderNumber |

| orderDate |

| requiredDate |

| shippedDate |

| status |

| comments |

| customerNumber |

+----------------+

7 rows in set (0.01 sec)

+-----------------+

| orderdetails |

+-----------------+

| orderNumber |

| productCode |

| quantityOrdered |

| priceEach |

| orderLineNumber |

+-----------------+

5 rows in set (0.00 sec)

SELECT

productCode,

ROUND(SUM(quantityOrdered * priceEach)) sales

FROM

orderdetails

INNER JOIN

orders USING (orderNumber)

WHERE

YEAR(shippedDate) = 2013

GROUP BY productCode

ORDER BY sales DESC

LIMIT 5;

运行结果：

+-------------+--------+

| productCode | sales |

+-------------+--------+

| S18_3232 | 103480 |

| S10_1949 | 67985 |

| S12_1108 | 59852 |

| S12_3891 | 57403 |

| S12_1099 | 56462 |

+-------------+--------+

5 rows in set (0.00 sec)

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

+--------------------+

| products |

+--------------------+

| productCode |

| productName |

| productLine |

| productScale |

| productVendor |

| productDescription |

| quantityInStock |

| buyPrice |

| MSRP |

+--------------------+

9 rows in set (0.01 sec)

SELECT

productName, sales

FROM

(SELECT

productCode,

ROUND(SUM(quantityOrdered * priceEach)) sales

FROM

orderdetails

INNER JOIN orders USING (orderNumber)

WHERE

YEAR(shippedDate) = 2013

GROUP BY productCode

ORDER BY sales DESC

LIMIT 5) top5products2013

INNER JOIN

products USING (productCode);

以下显示了上述查询的输出：

+-----------------------------+--------+

| 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 (0.00 sec)

在这个例子中：

首先，执行子查询以创建结果集或派生表。

然后，执行外部查询，使用productCode列将top5product2013派生表与products表联接起来。

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

假设你在2013年的客户分为3组：  platinum，gold，和silver。此外，您需要知道每个组中的客户数量，并满足以下条件：

订单量大于100K的白金客户

订购量在10K到100K之间的黄金客户

订单量小于10K的白银客户

要构造此查询，首先，您需要使用

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) = 2013

GROUP BY customerNumber;

以下是查询的输出：

+----------------+--------+---------------+

| customerNumber | sales | customerGroup |

+----------------+--------+---------------+

| 103 | 14571 | Gold |

| 112 | 32642 | Gold |

| 114 | 53429 | Gold |

| 121 | 51710 | Gold |

| 124 | 167783 | Platinum |

| 128 | 34651 | Gold |

| 129 | 40462 | Gold |

| 131 | 22293 | Gold |

| 141 | 189840 | Platinum |

| 144 | 7675 | Silver |

...

然后，您可以将此查询用作派生表并执行分组，如下所示：

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) = 2013

GROUP BY customerNumber) cg

GROUP BY cg.customerGroup;

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

+---------------+------------+

| customerGroup | groupCount |

+---------------+------------+

| Gold | 61 |

| Platinum | 4 |

| Silver | 8 |

+---------------+------------+

3 rows in set (0.01 sec)

在本教程中，您学习了如何使用作为FROM子句中的子查询的MySQL派生表来简化复杂查询。