SQL内 连接(只连接匹配的行)

/*==============================================================*/
/* 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 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 |
+----+-------------+--------+------------+
4 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 a1.region_name region, a2.store_name, a2.sales sales 
from geography a1, store_information a2 
where a1.store_name = a2.store_name;

select a1.region_name region, a2.store_name, a2.sales sales 
from geography a1 inner join store_information a2 
on a1.store_name = a2.store_name;

+--------+-------------+--------+
| region | store_name  | sales  |
+--------+-------------+--------+
| West   | Los Angeles | 150.20 |
| West   | San Diego   | 250.30 |
| West   | Los Angeles |  20.20 |
| East   | Boston      | 700.20 |
+--------+-------------+--------+

/* 每一区 (region_name) 的营业额 (sales) */
select a1.region_name region, sum(a2.sales) sales 
from geography a1, store_information a2 
where a1.store_name = a2.store_name 
group by a1.region_name

/* 每一区 (region_name) 的营业额 (sales) */
select a1.region_name region, sum(a2.sales) sales 
from geography a1 inner join store_information a2 
on a1.store_name = a2.store_name 
group by a1.region_name

+--------+--------+
| region | sales  |
+--------+--------+
| East   | 700.20 |
| West   | 420.70 |
+--------+--------+