Mysql 必知必会原文阅读笔记六（子查询）

本文主要是介绍Mysql 必知必会原文阅读笔记六（子查询），对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

一、xmind笔记，子查询

 

二、实操

1、理解子查询

简单说来就是，sql 查询语句可以嵌套。在写sql语句的时候尽量注意美化sql，不然sql语句非常难懂。如下面sql：

SELECT * from o_export_additional 
WHERE order_no  IN (SELECT order_no 
                                        FROM o_order)

2、子查询过滤

 3、利用子查询作为计算字段

这里我想直接应用原文的例子，这样比较清晰；

customers 表 cust_id, cust_name, cust_state

orders表有，cust_id, 还有order信息

Using Subqueries As Calculated Fields Another way to use subqueries is in creating calculated fields. Suppose you want to display the total number of orders placed by every customer in your customers table. Orders are stored in the orders table along with the appropriate customer ID. To perform this operation, followthese steps: 1. Retrieve the list of customers from the customers table. 2. For each customer retrieved, count the number of associated orders in the orders table. As you learned in the previous two chapters, you can use SELECT COUNT(*) to count rows in a table, and by providing a WHERE clause to filter a specific customer ID, you can count just that customer's orders. For example, the following code counts the number of orders placed by customer 10001 : • Input SELECT COUNT(*) AS orders FROM orders WHERE cust_id = 10001; 如果只用一个sql解决，那就是用子查询了，见下面sql： To perform that COUNT(*) calculation for each customer, use COUNT* as a subquery. 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 +----------------+------------+--------+ | cust_name | cust_state | orders | +----------------+------------+--------+ | Coyote Inc. | MI | 2 | | E Fudd | IL | 1 | | Mouse House | OH | 0 | | Wascals | IN | 1 | | Yosemite Place | AZ | 1 | +----------------+------------+--------+

这篇关于Mysql 必知必会原文阅读笔记六（子查询）的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！