一、数据准备
-- 创建表并插入数据
CREATE TABLE `saleorder` (
`order_id` int ,
`order_time` date ,
`order_num` int
)
INSERT INTO `saleorder` VALUES
(1, '2020-04-20', 420),
(2, '2020-04-04', 800),
(3, '2020-03-28', 500),
(4, '2020-03-13', 100),
(5, '2020-02-27', 300),
(6, '2020-01-07', 450),
(7, '2019-04-07', 800),
(8, '2019-03-15', 1200),
(9, '2019-02-17', 200),
(10, '2019-02-07', 600),
(11, '2019-01-13', 300);
查看表信息
select * from saleorder;
二、占比
写法一:
基本思路:用式内连接,外加嵌套找出分子分母,相除(最后要分组)
SELECT
order_month,
num,
total,
round( num / total, 2 ) AS ratio
FROM
(
-- 月统计
SELECT
DATE_FORMAT(order_time,"yyyy-MM") AS order_month,
sum( order_num ) AS num
FROM
saleorder
GROUP BY
DATE_FORMAT(order_time,"yyyy-MM")
)t1,
(
-- 年统计
SELECT
year(order_time) AS order_year,
sum( order_num ) AS total
FROM
saleorder
GROUP BY
year(order_time)
)t2
where substr(t1.order_month,1,4) = t2.order_year;
写法二:显示内连接
基本思路:显示内联接,先