Using subqueries as calculated fields
Another way to use subqueries is to create calculated fields. Suppose you need to display the total number of orders for each customer in the customers table. Orders are stored in the orders table with corresponding customer IDs.
In order to perform this operation, follow the steps below.
(1) Retrieve the customer list from the customers table.
(2) For each retrieved customer, count the number of orders in the orders table.
As mentioned in the previous two chapters, you can use SELECT COUNT (*) to count the rows in the table, and by providing a WHERE clause to filter for a specific customer ID, you can filter only for that customer Orders are counted. For example, the following code counts orders for customer 10001:
Input:
select count(*) as orders from orders where cust_id = 10001;
To perform a COUNT(*) calculation for each customer, COUNT(*) should be used as a subquery . Please look at the following code:
Input:
select cust_name,cust_state,(select count(*) from orders where orders.cust_id = customers.cust_id) as orders from customers order by cust_name;
Output:
## Analysis: This SELECT statement applies to each customer in the customers table Customer returns 3 columns: cust_name, cust_state, and orders. orders is a calculated field established by a subquery in parentheses. This subquery is executed once for each customer retrieved. In this example, the subquery was executed 5 times because 5 customers were retrieved. The WHERE clause in the subquery is slightly different from the WHERE clause used previously because it uses fully qualified column names (first mentioned in Chapter 4). The following statement tells SQL to compare the cust_id in the orders table with the cust_id currently being retrieved from the customers table:where orders.cust_id = customers.cust_id
select cust_name,cust_state,(select count(*) from orders where cust_id = cust_id) as orders from customers order by cust_name;
More than one solution As stated earlier in this chapter, although the sample code given here works well, it is not the most efficient way to solve this kind of data retrieval. We will encounter this example again in later chapters.
Build a query by gradually adding subqueries. Testing and debugging queries with subqueries can be tricky, especially as the complexity of these statements increases. The most reliable way to build (and test) a query with subqueries is to do it incrementally, much the same way MySQL handles them. First, build and test the innermost query. Then, build and test the outer query with hard-coded data, and only embed the subquery after confirming that it works. At this point, test it again. Repeat these steps for each query you want to add. Doing this adds only a small amount of time to constructing the query, but saves a lot of time later (finding out why the query is not working) and greatly increases the likelihood that the query will work properly in the first place.
[Related recommendations]
1. What is a mysql subquery? How to filter using subqueries?
2.What are connection and relationship tables in mysql?
3. Why use joins and how to create joins
4.The importance of WHERE clause in MySQL and how to join multiple tables
The above is the detailed content of Tutorial on creating calculated fields using subqueries in mysql. For more information, please follow other related articles on the PHP Chinese website!