子查询

最新推荐文章于 2023-11-07 16:26:23 发布

种子之家

最新推荐文章于 2023-11-07 16:26:23 发布

阅读量387

点赞数

分类专栏： MySql 文章标签： insert subquery date table sql mysql

本文链接：https://blog.csdn.net/seeds_home/article/details/7882485

版权

MySql 专栏收录该内容

41 篇文章 0 订阅

订阅专栏

我们可以在一个 SQL 语句中放入另一个 SQL 语句。当我们在 WHERE 子句或WHERE 子句或 HAVING子句中插入另一个 SQL 语句时，我们就有一个 subquery 的架构。 Subquery 的作用是什么呢？第一，它可以被用来连接表格。另外，有的时候 subquery 是唯一能够连接两个表格的方式。

/*==============================================================*/
/* DBMS name:      MySQL 5.0                                    */
/* Created on:     2012/8/19 8:45:30                            */
/*==============================================================*/


drop table if exists store_information;

drop table if exists geography;


/*==============================================================*/
/* Table: store_information                                     */
/*==============================================================*/
create table store_information
(
	 no                   int not null,
   store_name           varchar(20),
   sales                decimal(10,2),
   date                 date,
   primary key (no)
);

/*==============================================================*/
/* Table: geography                                             */
/*==============================================================*/
create table geography
(
	 no										int not null,
   region_name          varchar(20),
   store_name           varchar(20),
   primary key (no)
);

delete from store_information;
delete from geography;

insert store_information(no, store_name, sales, date) values(1, "Los Angeles", 150.2, '2008.12.01');
insert store_information(no, store_name, sales, date) values(2, "San Diego", 250.3, '2008.01.01');
insert store_information(no, store_name, sales, date) values(3, "Los Angeles", 20.2, '2008.02.01');
insert store_information(no, store_name, sales, date) values(4, "Boston", 700.2, '2008.06.11');
insert store_information(no, store_name, sales, date) values(5, "Guangzhou", 790.25, '2012.06.11');

insert geography(no, region_name, store_name) values(1, "East", "Boston");
insert geography(no, region_name, store_name) values(2, "East", "New York");
insert geography(no, region_name, store_name) values(3, "West", "Los Angeles");
insert geography(no, region_name, store_name) values(4, "West", "San Diego");

select * from store_information;
select * from geography;


mysql> select * from store_information;
+----+-------------+--------+------------+
| no | store_name  | sales  | date       |
+----+-------------+--------+------------+
|  1 | Los Angeles | 150.20 | 2008-12-01 |
|  2 | San Diego   | 250.30 | 2008-01-01 |
|  3 | Los Angeles |  20.20 | 2008-02-01 |
|  4 | Boston      | 700.20 | 2008-06-11 |
|  5 | Guangzhou   | 790.25 | 2012-06-11 |
+----+-------------+--------+------------+
5 rows in set (0.00 sec)


mysql> select * from geography;
+----+-------------+-------------+
| no | region_name | store_name  |
+----+-------------+-------------+
|  1 | East        | Boston      |
|  2 | East        | New York    |
|  3 | West        | Los Angeles |
|  4 | West        | San Diego   |
+----+-------------+-------------+
/* 所有在西部的店的营业额 */
select sum(sales) from store_information
where store_name in
(
	select store_name 
	from geography 
	where region_name = 'west'
)

select a1.region_name region, sum(a2.sales) sales   
from geography a1 inner join store_information a2   
on a1.store_name = a2.store_name 
where a1.region_name = "west"
group by a1.region_name;

+------------+
| sum(sales) |
+------------+
|     420.70 |
+------------+