订单表tbl_table用户表tbl_user1.2020年

SELECT DISTINCT city
FROM
  (SELECT b.city,
          sum(CASE
                  WHEN a.orderdate BETWEEN '2020-01-01 00:00:00' AND '2020-03-31 23:59:59' THEN a.salesamount
                  ELSE 0
              END) AS sum_1,
          sum(CASE
                  WHEN a.orderdate BETWEEN '2019-01-01 00:00:00' AND '2019-03-31 23:59:59' THEN a.salesamount
                  ELSE 0
              END) AS sum_0
   FROM tbl_table a
   LEFT JOIN tbl_user b ON a.userid = b.userid
   GROUP BY b.city ) AS c
ORDER BY (sum_1-sum_0)/sum_0
LIMIT 3 ；

（1）
select t1.City
from 
    (select u.City,sum(t.salesamout) as sales-20 from tbl_table t
    left join tbl_user u on t.userid=u.userid where year(orderdate)=2020 and month(orderdate) between 1 and 3
    group by u.City) t1
left join 
    (select u.City,sum(t.salesamout) as sales-19 from tbl_table t
    left join tbl_user u on t.userid=u.userid where year(orderdate)=2019 and month(orderdate) between 1 and 3
    group by u.City) t2
on t1.City=t2.City
order by ((sales-20-sales-19)/sales-19) 
limit 3
(2)
select u.Province,count(category)*2 as kucun
from tbl_table t
left join tbl_user u on t.userid=u.userid 
where year(orderdate)=2019 and month(orderdate)=11 and day(orderdate)=11
and t.category='轮胎'
group by u.Province

select 
    *
from 
(
select 
    city,
    sum(case when period = '今年' then sale_sum end) as this_year_sum,
    sum(case when period = '去年' then sale_sum end) as last_year_sum,
    sum(case when period = '今年' then sale_sum end)/sum(case when period = '去年' then sale_sum end) as growth --增长率
from
(
    select 
        City,
        case 
            when dt between '2020-01-01' and '2020-03-31' then '今年'
            when dt between '2019-01-01' and '2019-03-31' then '去年'
            else null
        end as period,
        sum(salesamount) as sale_sum
    from 
    (
        select 
            City,
            to_date(orderdate) as dt,
            orderid,
            userid,
            salesamount
        from 
            tbl_table
        where 
            (to_date(orderdate) between '2020-01-01' and '2020-03-31')
           &nbs***bsp;(to_date(orderdate) between '2019-01-01' and '2019-03-31')
    )t0
    left join 
    (
        select 
            userid,
            City
        from 
            tbl_user
    )t1
    on t0. userid = t1. userid
    group by 
        City,
        case 
            when dt between '2021-01-01' and '2021-03-31' then '今年'
            when dt between '2020-01-01' and '2020-03-31' then '去年'
            else null
        end
)dbase
group by 
    city
)a
order by growth asc --增长率升序，第一位增长率最低
limit 3
;

生成数据进行验证

CREATE TABLE IF NOT EXISTS tbl_table(
orderid INT(6) NOT NULL PRIMARY KEY,
userid VARCHAR(20) NOT NULL,
orderdate DATETIME NOT NULL,
pid VARCHAR(20) NOT NULL,
salesamount NUMERIC(10,2) NOT NULL,
category VARCHAR(20) NOT NULL)
INSERT INTO tbl_table VALUES(1,3227,'2019-02-02','tr-dp',100,'轮胎'),
(2,3228,'2019-02-02','tr-dp',200,'轮胎'),
(3,3229,'2020-03-02','tr-dp',150,'保养'),
(4,3210,'2020-03-02','tr-dp',250,'轮胎'),
(5,3211,'2020-03-02','tr-dp',250,'轮胎'),
(6,3212,'2019-03-02','tr-dp',250,'轮胎'),
(7,3511,'2020-03-02','tr-dp',250,'轮胎'),
(8,3512,'2019-03-02','tr-dp',250,'轮胎');
SELECT * FROM tbl_table;
CREATE TABLE IF NOT EXISTS tbl_user (
userid VARCHAR(20) NOT NULL PRIMARY KEY,
province VARCHAR(20) NOT NULL,
city VARCHAR(20) NOT NULL);
INSERT INTO tbl_user VALUES(3227,'浙江','杭州'),
(3228,'广东','广州'),
(3229,'浙江','杭州'),
(3210,'广东','广州'),
(3211,'江西','南昌'),
(3212,'江西','南昌'),
(3511,'北京','北京'),
(3512,'北京','北京');
SELECT * FROM tbl_user;
##第一问
WITH t1 AS (SELECT tu.city,SUM(salesamount) AS cnt FROM tbl_table tt
LEFT JOIN tbl_user tu
ON tt.userid=tu.userid
WHERE orderdate BETWEEN '2019-01-01' AND '2019-03-31'
GROUP BY city) ,t2 AS (SELECT tu.city,SUM(salesamount) AS cnt FROM tbl_table tt
LEFT JOIN tbl_user tu
ON tt.userid=tu.userid
WHERE orderdate BETWEEN '2020-01-01' AND '2020-03-31'
GROUP BY city) 
SELECT b.city,t_rank  FROM 
(SELECT t1.city,row_number() over (ORDER BY (t2.cnt-t1.cnt)/t1.cnt) AS t_rank 
FROM t1 
LEFT JOIN t2 
ON t1.city=t2.city ) b
WHERE t_rank <4
#第二问
SELECT province, COUNT(*)*2 FROM tbl_table tt
LEFT JOIN tbl_user tu
ON tt.userid=tu.userid
WHERE orderdate BETWEEN '2019-01-01' AND '2019-12-31' AND category LIKE '%轮胎%'
GROUP BY province