/*==============================================================*//* 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( noint 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 |+--------+--------+
