有三个表，分别是区域（district），城市（city）和订单（order）。区域中有两个字段分别是区域ID（disid）和区域名称（disname）；城市有两个字段分别是城市ID（cityid）和区域ID（disid）；订单有四个字段分别是订单ID（oid）、用户ID（userid）、城市ID（cityid）和消费金额（amount）。
首先定义一下高消费者是消费金额大于1W的用户，问题是生成以下报表：
| 区域名 | 高消费者人数 | 消费总额 |
|---|---|---|
| * | * | * |
| * | * | * |
建立三个表：
DROP TABLE IF EXISTS `district`;
CREATE TABLE `district` (
`disid` int(11) NOT NULL,
`disname` varchar(50) DEFAULT NULL,
PRIMARY KEY (`disid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `city`;
CREATE TABLE `city` (
`cityid` int(11) NOT NULL,
`disid` int(11) DEFAULT NULL,
PRIMARY KEY (`cityid`),
KEY `disid` (`disid`),
CONSTRAINT `disid` FOREIGN KEY (`disid`) REFERENCES `district` (`disid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `order`;
CREATE TABLE `order` (
`oid` int(11) NOT NULL,
`userid` int(11) DEFAULT NULL,
`cityid` int(11) DEFAULT NULL,
`amount` int(11) DEFAULT NULL,
PRIMARY KEY (`oid`),
KEY `cityid` (`cityid`),
CONSTRAINT `cityid` FOREIGN KEY (`cityid`) REFERENCES `city` (`cityid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into district values(1,"一区");
insert into district values(2,"二区");
insert into district values(3,"三区");
insert into district values(4,"四区");
insert into city values(1,1);
insert into city values(2,1);
insert into city values(3,1);
insert into city values(4,2);
insert into city values(5,2);
insert into city values(6,3);
insert into city values(7,3);
insert into city values(8,3);
insert into `order` values(1,1,1,4000);
insert into `order` values(2,1,1,3000);
insert into `order` values(3,1,1,5000);
insert into `order` values(4,2,2,6000);
insert into `order` values(5,3,2,2000);
insert into `order` values(6,3,2,9000);
insert into `order` values(7,4,4,7000);
insert into `order` values(8,5,5,11000);
insert into `order` values(9,6,6,7000);
insert into `order` values(10,6,6,6000);
insert into `order` values(11,7,6,12000);
insert into `order` values(12,8,7,21000);
insert into `order` values(13,9,8,18000);
思路是首先要对订单（order）表做一次统计查询，合并同一用户的不同订单并过滤出高消费者：
select userid,cityid,sum(amount) as amount from `order` group by userid having sum(amount)>10000;
结果：
| userid | cityid | amount |
|---|---|---|
| 1 | 1 | 12000 |
| 3 | 2 | 11000 |
| 5 | 5 | 11000 |
| 6 | 6 | 13000 |
| 7 | 6 | 12000 |
| 8 | 7 | 21000 |
| 9 | 8 | 18000 |
然后把区域表、城市表和以上结果做连接查询：
select d.disname as 区域名,count(t.userid) as 高消费者人数,sum(t.amount) as 消费总额
from
district d
left join
city c on d.disid = c.disid
left join
(select userid,cityid,sum(amount) as amount from `order` group by userid having sum(amount)>10000) t
on c.cityid = t.cityid
group by d.disid;
结果为：
| 区域名 | 高消费者人数 | 消费总额 |
|---|---|---|
| 一区 | 2 | 23000 |
| 二区 | 1 | 11000 |
| 三区 | 4 | 64000 |
| 四区 | 0 | NULL |
