Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use derivative tables in mysql. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have some understanding of the relevant knowledge after reading this article.

A derived table is a virtual table returned from a select statement. A derived table is similar to a temporary table, but it is much easier to use a derived table in a SELECT statement than a temporary table because it does not require the steps to create a temporary table. So when an independent subquery is used in the FROM clause of the SELECT statement, we call it a derived table. Without saying much nonsense, let's explain it in detail:

SELECT column_listFROM* (SELECT * column_list* FROM* table_1) derived_table_name;WHERE derived_table_name.column > 1...

The derived table is used where the asterisk is marked. In order to be more detailed, let's look at a concrete example. Next, we will get the top five products with the highest sales revenue in 2018 from the orders table and the orderdetails table in the database. Let's take a look at the fields in the following table:

Let's first take a look at the following sql:

SELECT productCode, ROUND (SUM (quantityOrdered * priceEach)) salesFROM orderdetails INNER JOIN orders USING (orderNumber) WHERE YEAR (shippedDate) = 2018GROUP BY productCodeORDER BY sales DESCLIMIT 5

This sql takes the common orderNumber field in the two tables as the node of the joint query. After it is finished, it takes the time as the condition, and then takes that productCode field as the grouping basis, obtains the grouping field and the nickname field after calculation, and then takes the sales field as the sorting basis, and finally extracts the first five results. That's probably what it's all about. We can think of the result set as a temporary table or something. Let's look at a result set:

+-+-+ | productCode | sales | +-+-+ | S18room3232 | 103480 | S10room1949 | 67985 | | S12room1108 | 59852 | | S1213891 | 57403 | | S12room1099 | 56462 | +-+-+ 5 rows in set

When done, since we are learning the derived table, we can certainly use the result of this query as the derived table and associate it with the products table. Where the structure of the products table is as follows:

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 (10L2) | NO | | NULL | | MSRP | decimal (10L2) | NO | | NULL | | +- -+-+ 20 rows in set

Now that we know the table structure, let's take a look at the following sql:

SELECT productName, salesFROM# (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_2018INNER JOIN products USING (productCode)

The above # part is our previous sql, which is easy for you to understand. I marked it with #, but you can't use it when you write it. When we're done, let's take a look at this sql. What does it mean? It just makes a simple federated query by treating the part we marked with # as a table. However, this table, which we call a derived table, will be cleared immediately after use, so we can consider using it when simplifying complex queries. Needless to say, let's take a look at the result set:

+-- +-- + | productName | sales | +-+-+ | 1992 Ferrari Spider red | 103480 | | 1952 Alpine Renault 1300 | 67985 | | 2001 Ferrari Enzo | 59852 | | 1969 Ford Falcon | 57403 | | 1968 Ford Mustang | 56462 | +-- +-+ 5 rows in set

Then, let's briefly sum up:

First, execute a subquery to create a result set or derived table.

Then, use the products table to join the external query of the top5_products_2018-derived table on the productCode column.

When it's over, this is the end of the understanding and use of the simple derivative table. Let's try a slightly more complicated one. Let's first assume that the 2018 customers must be divided into three groups: platinum, platinum and silver. In addition, you need to know the number of customers in each group, as follows:

Platinum customers with a total order of more than 100000

Gold customers with a total order of 10000 to 100000

Silver customers with a total order of less than 10000

To build this query, first, we need to use the case expression and the group by clause to put each customer into the appropriate group, as shown below:

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) customerGroupFROM orderdetails INNER JOIN orders USING (orderNumber) WHERE YEAR (shippedDate) = 2018GROUP BY customerNumber ORDER BY sales DESC

Let's look at an example of a result set:

+-+ | customerNumber | sales | customerGroup | +-+ | 189840 | Platinum | | 167783 | Platinum | | 150123 | Platinum | | 117635 | Platinum | 93565 | Gold | | 89876 | Gold | 89419 | Gold | * many data is omitted here * | | 219 | 4466 | Silver | | 2880 | Silver | 2756 | Silver | +-+-| +-+

When we're done, we can use the table obtained from the above query as a derived table to correlate the query and group it to get the desired data. Let's take a look at the following sql:

SELECT customerGroup, COUNT (cg.customerGroup) AS groupCountFROM (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) cgGROUP BY cg.customerGroup

What exactly does it mean? I believe that if you are smart, you must have a better understanding than I do, so we will not repeat it. When we're done, let's take a look at the result set:

+-+-+ | customerGroup | groupCount | +-+-+ | Gold | 61 | Platinum | 4 | Silver | 8 | +-+-+ 3 rows in set about How to use derived tables in mysql is shared here. I hope the above content can be of some help to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.