滴滴一道SQL面试题

有三个表，分别是区域（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;

结果：

然后把区域表、城市表和以上结果做连接查询：

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;

结果为：