数据库子查询50

weixin_51630304

于 2021-07-19 13:59:44 发布

阅读量57

点赞数

文章标签：数据库

本文链接：https://blog.csdn.net/weixin_51630304/article/details/118894369

版权

本文通过实例展示了如何使用子查询从多个数据库表中检索关联信息，以查询特定商品的用户订单详情。子查询可以嵌套在where从句中，也可以用在字段中，简化复杂查询。此外，还介绍了如何利用子查询统计每个用户当前的订单数量，从而提高查询效率。灵活运用子查询能够极大地简化SQL语句，提升数据库操作的便捷性。

摘要由CSDN通过智能技术生成

数据库子查询50

本文将从商品查询案例向大家介绍子查询
三个表：orders、orderitems、customers分别存储商品信息、订单信息、用户信息。当需要查询prod_id = 'TNT2’的商品订单用户的信息时。我们可能会如下查询：

select order_num from orderitems where prod_id = 'TNT2';
+-----------+
| order_num |
+-----------+
|     20005 |
|     20007 |
+-----------+
2 rows in set (0.00 sec)

select cust_id from orders where order_num in(20005,20007);
+---------+
| cust_id |
+---------+
|   10001 |
|   10004 |
+---------+
2 rows in set (0.00 sec)

select cust_name,cust_contact from customers where cust_id in(10001,10004);
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+
2 rows in set (0.00 sec)

上述的查询方式我们称之为简单查询（即从单个数据库表中检索数据的单条语句），子查询如下：

select cust_name,cust_contact 
from customers 
where cust_id in(select cust_id 
	from orders 
	where order_num in (select order_num 
		from orderitems 
		where prod_id = 'TNT2'));
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+
2 rows in set (0.00 sec)

上述的子查询是包含在where 从句中，也有字段中使用子查询的情况。
现在统计所有用户目前的订单数。
简单查询：

select cust_id from customers;
+---------+
| cust_id |
+---------+
|   10001 |
|   10002 |
|   10003 |
|   10004 |
|   10005 |
+---------+
5 rows in set (0.00 sec)

select count(*) from orders where cust_id = 10001;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.01 sec)

select count(*) from orders where cust_id = 10002;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

......依次查询

运用子查询：

select cust_id ,cust_name ,(select count(*)from orders where orders.cust_id = customers.cust_id ) orders from customers order by cust_name;
+---------+----------------+--------+
| cust_id | cust_name      | orders |
+---------+----------------+--------+
|   10001 | Coyote Inc.    |      2 |
|   10005 | E Fudd         |      1 |
|   10002 | Mouse House    |      0 |
|   10003 | Wascals        |      1 |
|   10004 | Yosemite Place |      1 |
+---------+----------------+--------+
5 rows in set (0.00 sec)