sql 解析过程

 

订单表：

drop table if exists `order`;
create table if not exists `order`(
    id int,
    user_id varchar(10),
    city varchar(50),
    order_time varchar(50)
)engine=InnoDB DEFAULT charset=utf8;

insert into `order` values(1,'A','深圳','2018-01-01 10:10:30'),
(2,'B','上海','2018-01-10 10:10:30'),
(3,'C','北京','2018-02-01 12:10:30'),
(4,'A','深圳','2018-01-14 21:10:30'),
(5,'C','成都','2018-01-18 10:10:30'),
(6,'D','广州','2018-01-22 10:10:30'),
(7,'Y','南宁','2018-03-16 04:10:30'),
(8,'F','天津','2018-03-29 09:10:30'),
(9,'T','北京','2018-01-09 10:10:30'),
(10,'F','天津','2018-01-01 09:10:30')
;

drop table if exists user;
create table if not exists user(
    user_id varchar(10),
    user_name varchar(20)
)engine=InnoDB DEFAULT charset=utf8;
insert into user values
('A','张三'),
('B','李四'),
('C','王五'),
('D','刘六'),
('Y','赵雪'),
('F','陈俊'),
('T','寒梅')
;

select 
id,
user_id,
city,
month(order_time) as month
from 
`order`;

+------+---------+--------+-------+
| id   | user_id | city   | month |
+------+---------+--------+-------+
|    1 | A       | 深圳   |     1 |
|    2 | B       | 上海   |     1 |
|    3 | C       | 北京   |     2 |
|    4 | A       | 深圳   |     1 |
|    5 | C       | 成都   |     1 |
|    6 | D       | 广州   |     1 |
|    7 | Y       | 南宁   |     3 |
|    8 | F       | 天津   |     3 |
|    9 | T       | 北京   |     1 |
|   10 | F       | 天津   |     1 |
+------+---------+--------+-------+

select
city,
sum(case when month=1 then 1 else 0 end) as 1月订单数,
sum(case when month=2 then 1 else 0 end) as 2月订单数,
sum(case when month=3 then 1 else 0 end) as 3月订单数
from(
    select 
    id,
    user_id,
    city,
    month(order_time) as month
    from 
    `order`
) t1
group by city
;

select
user_id,
city,
max(order_time) as 最后购买时间
from 
`order`
group by city
;

mysql> select
    -> user.user_name,
    -> t1.city,
    -> t1.last_paytime as 最后购买时间
    -> from
    -> user
    -> left join 
    -> (
    ->     select
    ->     user_id,
    ->     city,
    ->     max(order_time) as last_paytime
    ->     from 
    ->     `order`
    ->     group by city
    -> ) t1
    -> on user.user_id = t1.user_id
    -> ;
+-----------+--------+---------------------+
| user_name | city   | 最后购买时间        |
+-----------+--------+---------------------+
| 张三      | 深圳   | 2018-01-14 21:10:30 |
| 李四      | 上海   | 2018-01-10 10:10:30 |
| 王五      | 北京   | 2018-02-01 12:10:30 |
| 王五      | 成都   | 2018-01-18 10:10:30 |
| 刘六      | 广州   | 2018-01-22 10:10:30 |
| 赵雪      | 南宁   | 2018-03-16 04:10:30 |
| 陈俊      | 天津   | 2018-03-29 09:10:30 |
    | 寒梅      | NULL   | NULL                |
+-----------+--------+---------------------+
8 rows in set (0.02 sec)

mysql> select
    -> user.user_name,
    -> t1.city,
    -> t1.last_paytime as 最后购买时间
    -> from
    -> user
    -> left join 
    -> (
    ->     select
    ->     user_id,
    ->     city,
    ->     max(order_time) as last_paytime
    ->     from 
    ->     `order`
    ->     group by user_id,city
    -> ) t1
    -> on user.user_id = t1.user_id
    -> ;
+-----------+--------+---------------------+
| user_name | city   | 最后购买时间        |
+-----------+--------+---------------------+
| 张三      | 深圳   | 2018-01-14 21:10:30 |
| 李四      | 上海   | 2018-01-10 10:10:30 |
    | 王五      | 北京   | 2018-02-01 12:10:30 |
    | 王五      | 成都   | 2018-01-18 10:10:30 |
| 刘六      | 广州   | 2018-01-22 10:10:30 |
| 赵雪      | 南宁   | 2018-03-16 04:10:30 |
| 陈俊      | 天津   | 2018-03-29 09:10:30 |
| 寒梅      | 北京   | 2018-01-09 10:10:30 |
+-----------+--------+---------------------+

select
user.user_name,
t1.city,
max(t1.last_paytime) as 最后购买时间
from
user
left join 
(
    select
    user_id,
    city,
    max(order_time) as last_paytime
    from 
    `order`
    group by user_id,city
) t1
on user.user_id = t1.user_id
group by user_name
;

mysql> select
    -> user.user_name,
    -> t1.city,
    -> max(t1.last_paytime) as 最后购买时间
    -> from
    -> user
    -> left join 
    -> (
    ->     select
    ->     user_id,
    ->     city,
    ->     max(order_time) as last_paytime
    ->     from 
    ->     `order`
    ->     group by user_id,city
    -> ) t1
    -> on user.user_id = t1.user_id
    -> group by user_name
    -> ;
+-----------+--------+---------------------+
| user_name | city   | 最后购买时间        |
+-----------+--------+---------------------+
| 刘六      | 广州   | 2018-01-22 10:10:30 |
| 寒梅      | 北京   | 2018-01-09 10:10:30 |
| 张三      | 深圳   | 2018-01-14 21:10:30 |
| 李四      | 上海   | 2018-01-10 10:10:30 |
| 王五      | 北京   | 2018-02-01 12:10:30 |
| 赵雪      | 南宁   | 2018-03-16 04:10:30 |
| 陈俊      | 天津   | 2018-03-29 09:10:30 |
+-----------+--------+---------------------+