拼多多真题解析

一

题目

CREATE TABLE orders(
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_time TIMESTAMP,
    cate VARCHAR(255),
    goods_id int,
    order_amount int
)

image.png

1.请统计2018年全年销售金额，并返回如下形式：

日期             销售金额
2018-01       *****

2018-02       *****

2.请统计2018年每月销售金额，以及金额排名。
3.请用SQL选出2018年2月每个类目销量最高的2个爆款商品以及排名先后。

答案

SELECT DATE_FORMAT(t.order_time,'%Y-%m') AS '日期', SUM(t.order_amount) AS '销售金额'
FROM orders t
WHERE YEAR(t.order_time) = 2018
GROUP BY MONTH(t.order_time)

SELECT a.mon AS r,a.sum AS x,
CASE 
WHEN @prevRank = a.sum THEN @curRank 
WHEN @prevRank := a.sum THEN @curRank := @curRank + 1
END AS j
FROM 
(SELECT DATE_FORMAT(t.order_time,'%Y-%m') AS mon, SUM(t.order_amount) AS sum
FROM orders t
WHERE YEAR(t.order_time) = 2018
GROUP BY MONTH(t.order_time)
ORDER BY SUM(t.order_amount) DESC) a,(SELECT @curRank :=0, @prevRank := NULL) b

SELECT t.cate AS '类目',t.goods_id AS '商品id',t.rankNO AS '排名'
FROM
(SELECT
    a.cate,a.goods_id,a.count,
    @rank:= CASE WHEN @prevCate=a.cate THEN @rank+1 ELSE 1 END AS rankNO,
    @prevCate:=a.cate AS type
FROM
(
        SELECT t.cate,t.goods_id,count(goods_id) AS count
        FROM orders t
        WHERE date_format(t.order_time, '%Y%m%d%H%i%s')LIKE "2018%"
        GROUP BY t.goods_id
        ORDER BY t.cate,count(t.goods_id) DESC
) AS a,(SELECT @rank:=0 ,@prevCate:='') b) t
WHERE t.rankNO <= 2

反思

1.DATE_FORMAT(date,format)#对日期进行格式化处理
2.补充
 %y  年，两位
%Y  年，四位
%m 月，01-12
%M 月名
%d  天
%a 星期
%h 小时