所谓“环比”即连续2个单位周期（比如连续两月）内的量的变化比。如今年8月比今年7月。 当然这里的单位不一定是月,可以是任何时间单位。
环比增长率=（本期数-上期数）/上期数×100%。
反映本期比上期增长了多少；环比发展速度，一般是指报告期水平与前一时期水平之比，表明现象逐期的发展速度。
所谓“同比”即同期比较，可以是本年度与上年度，也可以是上年同一个月份或季度与本年同期。
同比增长率=（本期数-同期数）/|同期数|×100%。
按年月统计不同年份的销售总值，并计算环比（销售总额同比上期）、同比（销售总额同比去年同期）
查询订单销售表结构
CREATE TABLE `food_orders_info` (
`id` varchar(36) NOT NULL COMMENT ‘主键id‘,
`pid` varchar(36) NOT NULL COMMENT ‘主订单id‘,
`order_no` varchar(30) NOT NULL COMMENT ‘订单号生成规则 3大写英文-时间戳-3随机码(ZXC-201906241558-SX2)‘,
`merchant_id` varchar(36) NOT NULL COMMENT ‘订单归属的经销商id‘,
`buyer_id` varchar(36) NOT NULL COMMENT ‘买家id‘,
`buyer_name` varchar(100) NOT NULL COMMENT ‘买家名称‘,
`order_money` decimal(10,2) NOT NULL COMMENT ‘订单金额‘,
`out_stock_money` decimal(10,2) DEFAULT NULL COMMENT ‘出库金额‘,
`freight_money` decimal(10,2) NOT NULL COMMENT ‘运费‘,
`driver_id` varchar(36) DEFAULT NULL COMMENT ‘司机id‘,
`driver_name` varchar(36) DEFAULT NULL COMMENT ‘司机名称‘,
`payment_state` bit(1) NOT NULL DEFAULT b‘0‘ COMMENT ‘支付状态（0=未支付，1=已支付）‘,
`payment_type` varchar(20) DEFAULT NULL COMMENT ‘支付方式（支付宝，微信，xx银行），预留字段暂不填写‘,
`order_state` varchar(10) NOT NULL COMMENT ‘订单状态（00=预约单，10=待分拣，20=分拣中,30=待配送，40=配送中，50=待确认，60=已完成）‘,
`abnormal_state` bit(1) NOT NULL DEFAULT b‘1‘ COMMENT ‘异常状态，0=异常，1=正常‘,
`order_resource` varchar(20) NOT NULL COMMENT ‘订单来源，暂时只有微信公众号‘,
`receive_address` varchar(128) NOT NULL COMMENT ‘收货地址‘,
`receive_man` varchar(100) NOT NULL COMMENT ‘收货人名称‘,
`receive_tel` varchar(20) NOT NULL COMMENT ‘收货人联系电话‘,
`address_longitude` varchar(36) DEFAULT NULL COMMENT ‘地址经度‘,
`address_latitude` varchar(36) DEFAULT NULL,
`delivery_date_start` timestamp NULL DEFAULT NULL COMMENT ‘配送开始时间‘,
`delivery_date_end` timestamp NULL DEFAULT NULL COMMENT ‘配送时间结束时间‘,
`receive_date_start` timestamp NULL DEFAULT NULL COMMENT ‘收货时间-开始时间‘,
`receive_date_end` timestamp NULL DEFAULT NULL COMMENT ‘收货时间-截止时间‘,
`receive_date_sure` timestamp NULL DEFAULT NULL COMMENT ‘确认收货时间‘,
`if_end` bit(1) NOT NULL COMMENT ‘订单是否结束(0=未结束，1=结束)‘,
`remark` varchar(512) DEFAULT NULL COMMENT ‘订单备注‘,
`bill_id` varchar(36) DEFAULT NULL COMMENT ‘票据id,为空时表示该订单不开票‘,
`sort` tinyint(4) NOT NULL DEFAULT ‘1‘ COMMENT ‘排序码‘,
`state` tinyint(1) NOT NULL DEFAULT ‘1‘ COMMENT ‘状态（0=删除，1=正常）‘,
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间（下单时间）‘,
`create_user` varchar(36) NOT NULL COMMENT ‘创建人‘,
`create_user_name` varchar(128) DEFAULT NULL,
`last_modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘最后修改时间‘,
`last_modify_user` varchar(36) NOT NULL COMMENT ‘最后修改人‘,
`last_modify_user_name` varchar(128) DEFAULT NULL,
`order_time_type` varchar(1) NOT NULL DEFAULT ‘1‘ COMMENT ‘订单类型，0预约单/1正常单/2加急单‘,
`reduction_money` decimal(10,2) DEFAULT ‘0.00‘ COMMENT ‘满减金额‘,
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT=‘订单表‘;
备注：需要统计的是每个月的销售金额，而销售金额=出库金额-运费，即
sale_money= out_stock_money-freight_money；
create_time字段为下单时间，这里可以理解为销售时间
计算环比
思路：表a统计出当前年月份的销售总额，表b统计出上一个月的销售总额（在表a的基础上加上一个月，可得到上个月的销售量），然后再将两个表进行左联接（通过now_time进行关联）。
#当前月份的销售额
select
date_format(a.create_time,‘%Y-%m‘) as now_time,
sum( a.out_stock_money) AS a_out_money,
sum( a.freight_money) AS a_fre_money,
CONVERT (sum( a.out_stock_money)-sum( a.freight_money),DECIMAL ( 10,2 )) as a_sale_money
from food_orders_info a
where a.state=1
and a.order_state=60
GROUP BY date_format(a.create_time,‘%Y-%m‘)
#上个月的销售额
select
date_format(DATE_ADD(b.create_time,INTERVAL 1 MONTH ), ‘%Y-%m‘) as now_time,
sum( b.out_stock_money) AS bout_money,
sum( b.freight_money) AS bfe_money,
CONVERT (sum(b.out_stock_money)-sum( b.freight_money),DECIMAL ( 10,2 )) as bsale_money
from food_orders_info b
where b.state=1
and b.order_state=60
GROUP BY date_format(DATE_ADD(b.create_time,INTERVAL 1 MONTH ), ‘%Y-%m‘)
备注：
DATE_ADD(b.create_time,INTERVAL 1 MONTH ) #表示加一个月
#当前月和上月销售额进行关联
SELECT
now_sale.now_time,
now_sale.now_year,
CASE
WHEN a_sale_money IS NULL
OR a_sale_money = 0 THEN
0 ELSE a_sale_money
END this_sale_money,
CASE
WHEN b_sale_money IS NULL
OR b_sale_money = 0 THEN
0 ELSE b_sale_money
END last_month_money,
CASE
WHEN b_sale_money IS NULL
OR b_sale_money = 0 THEN
0 ELSE ( CONVERT ( ( ( a_sale_money - b_sale_money ) / b_sale_money ) * 100, DECIMAL ( 10, 2 ) ) )
END month_ratio
FROM
(
SELECT
date_format( a.create_time, ‘%Y-%m‘ ) AS now_time,
date_format( a.create_time, ‘%Y‘ ) AS now_year,
sum( a.out_stock_money ) AS a_out_money,
sum( a.freight_money ) AS a_fre_money,
CONVERT ( sum( a.out_stock_money ) - sum( a.freight_money ), DECIMAL ( 10, 2 ) ) AS a_sale_money
FROM
food_orders_info a
WHERE
a.state = 1
AND a.order_state = 60
GROUP BY
date_format( a.create_time, ‘%Y-%m‘ ),
date_format( a.create_time, ‘%Y‘ )
ORDER BY
date_format( a.create_time, ‘%Y-%m‘ ) ASC
) now_sale
LEFT JOIN (
SELECT
date_format( DATE_ADD( b.create_time, INTERVAL 1 MONTH ), ‘%Y-%m‘ ) AS now_time,
sum( b.out_stock_money ) AS bout_money,
sum( b.freight_money ) AS bfe_money,
CONVERT ( sum( b.out_stock_money ) - sum( b.freight_money ), DECIMAL ( 10, 2 ) ) AS b_sale_money
FROM
food_orders_info b
WHERE
b.state = 1
AND b.order_state = 60
GROUP BY
date_format( DATE_ADD( b.create_time, INTERVAL 1 MONTH ), ‘%Y-%m‘ )
ORDER BY
date_format( DATE_ADD( b.create_time, INTERVAL 1 MONTH ), ‘%Y-%m‘ ) ASC
) old_sale ON now_sale.now_time = old_sale.now_time
ORDER BY
now_sale.now_year DESC,
now_sale.now_time ASC
得到的关联表
由于数据库销售金额有为空的情况，所以的加上判断条件，让为空或被除数为空的值，返回0。
再查询去年同期的销售金额
SELECT
date_format( DATE_ADD( c.create_time, INTERVAL 1 YEAR ), ‘%Y-%m‘ ) AS last_year_time,
sum( c.out_stock_money ) AS bout_money,
sum( c.freight_money ) AS bfe_money,
CONVERT ( sum( c.out_stock_money ) - sum( c.freight_money ), DECIMAL ( 10, 2 ) ) AS last_year_money
FROM
food_orders_info c
WHERE
c.state = 1
AND c.order_state = 60
GROUP BY
date_format( DATE_ADD( c.create_time, INTERVAL 1 YEAR ), ‘%Y-%m‘ )
DATE_ADD( c.create_time, INTERVAL 1 YEAR ) 表示加一年。
得到的表如下：
最后，整合当前年月销售、上个月销售、去年同期销售。
SELECT
now_sale.now_time,
now_sale.now_year,
CASE
WHEN a_sale_money IS NULL
OR a_sale_money = 0 THEN
0 ELSE a_sale_money
END this_sale_money,
CASE
WHEN b_sale_money IS NULL
OR b_sale_money = 0 THEN
0 ELSE b_sale_money
END last_month_money,
CASE
WHEN last_year_money IS NULL
OR last_year_money = 0 THEN
0 ELSE last_year_money
END last_year_money,
CASE
WHEN b_sale_money IS NULL
OR b_sale_money = 0 THEN
0 ELSE ( CONVERT ( ( ( a_sale_money - b_sale_money ) / b_sale_money ) * 100, DECIMAL ( 10, 2 ) ) )
END month_ratio,
CASE
WHEN last_year_money IS NULL
OR last_year_money = 0 THEN
0 ELSE ( CONVERT ( ( ( a_sale_money - last_year_money ) / last_year_money ) * 100, DECIMAL ( 10, 2 ) ) )
END year_ratio
FROM
(
SELECT
date_format( a.create_time, ‘%Y-%m‘ ) AS now_time,
date_format( a.create_time, ‘%Y‘ ) AS now_year,
sum( a.out_stock_money ) AS a_out_money,
sum( a.freight_money ) AS a_fre_money,
CONVERT ( sum( a.out_stock_money ) - sum( a.freight_money ), DECIMAL ( 10, 2 ) ) AS a_sale_money
FROM
food_orders_info a
WHERE
a.state = 1
AND a.order_state = 60
GROUP BY
date_format( a.create_time, ‘%Y-%m‘ ),
date_format( a.create_time, ‘%Y‘ )
ORDER BY
date_format( a.create_time, ‘%Y-%m‘ ) ASC
) now_sale
LEFT JOIN (
SELECT
date_format( DATE_ADD( b.create_time, INTERVAL 1 MONTH ), ‘%Y-%m‘ ) AS now_time,
sum( b.out_stock_money ) AS bout_money,
sum( b.freight_money ) AS bfe_money,
CONVERT ( sum( b.out_stock_money ) - sum( b.freight_money ), DECIMAL ( 10, 2 ) ) AS b_sale_money
FROM
food_orders_info b
WHERE
b.state = 1
AND b.order_state = 60
GROUP BY
date_format( DATE_ADD( b.create_time, INTERVAL 1 MONTH ), ‘%Y-%m‘ )
ORDER BY
date_format( DATE_ADD( b.create_time, INTERVAL 1 MONTH ), ‘%Y-%m‘ ) ASC
) old_sale ON now_sale.now_time = old_sale.now_time
LEFT JOIN (
SELECT
date_format( DATE_ADD( c.create_time, INTERVAL 1 YEAR ), ‘%Y-%m‘ ) AS last_year_time,
sum( c.out_stock_money ) AS bout_money,
sum( c.freight_money ) AS bfe_money,
CONVERT ( sum( c.out_stock_money ) - sum( c.freight_money ), DECIMAL ( 10, 2 ) ) AS last_year_money
FROM
food_orders_info c
WHERE
c.state = 1
AND c.order_state = 60
GROUP BY
date_format( DATE_ADD( c.create_time, INTERVAL 1 YEAR ), ‘%Y-%m‘ )
ORDER BY
date_format( DATE_ADD( c.create_time, INTERVAL 1 YEAR ), ‘%Y-%m‘ ) ASC
) last_year_sale ON now_sale.now_time = last_year_sale.last_year_time
ORDER BY
now_sale.now_year DESC,
now_sale.now_time ASC
查询得到
month_radio即环比，year_ratio即同比。
查询季度主要使用 QUARTER ( 时间)函数
方法和按年月统计类似，就直接上sql语句了
#查询当前季度销售总额
SELECT
date_format( a.create_time, ‘%Y‘ ) AS now_year,
QUARTER ( a.create_time ) a_quarter,
CONCAT( YEAR ( a.create_time ), QUARTER ( a.create_time ) ) now_quarter,
sum( a.out_stock_money ) AS a_out_money,
sum( a.freight_money ) AS a_fre_money,
CONVERT ( sum( a.out_stock_money ) - sum( a.freight_money ), DECIMAL ( 10, 2 ) ) AS a_sale_money
FROM
food_orders_info a
WHERE
a.state = 1
AND a.order_state = 60
GROUP BY
CONCAT( YEAR ( a.create_time ), QUARTER ( a.create_time )),date_format( a.create_time, ‘%Y‘ ), QUARTER ( a.create_time )
QUARTER ( a.create_time ) 指查询当前时间在哪一个季度
查询上一季度销售总额
SELECT
CONCAT( YEAR ( b.create_time ), QUARTER ( DATE_ADD( b.create_time, INTERVAL 1 QUARTER ) ) ) AS last_quarter,
sum( b.out_stock_money ) AS b_out_money,
sum( b.freight_money ) AS b_fre_money,
CONVERT ( sum( b.out_stock_money ) - sum( b.freight_money ), DECIMAL ( 10, 2 ) ) AS b_sale_money
FROM
food_orders_info b
WHERE
b.state = 1
AND b.order_state = 60
GROUP BY
CONCAT( YEAR ( b.create_time ), QUARTER ( DATE_ADD( b.create_time, INTERVAL 1 QUARTER ) ) )
查询去年同季度销售额
SELECT
CONCAT(YEAR( DATE_ADD( c.create_time, INTERVAL 1 year )),QUARTER(c.create_time)) as last_year_quarter,
sum( c.out_stock_money ) AS c_out_money,
sum( c.freight_money ) AS c_fre_money,
CONVERT ( sum( c.out_stock_money ) - sum( c.freight_money ), DECIMAL ( 10, 2 ) ) AS c_sale_money
FROM
food_orders_info c
WHERE
c.state = 1
AND c.order_state = 60
GROUP BY
CONCAT(YEAR( DATE_ADD( c.create_time, INTERVAL 1 year )),QUARTER(c.create_time))
查询本季度、上一季度的销售额，并统计环比
SELECT
now_quarter_sale.now_year,
now_quarter_sale.a_quarter,
now_quarter_sale.now_quarter,
CASE
WHEN now_quarter_sale.a_sale_money IS NULL
OR now_quarter_sale.a_sale_money = 0 THEN
0 ELSE now_quarter_sale.a_sale_money
END this_sale_money,
CASE
WHEN last_quarter_sale.b_sale_money IS NULL
OR last_quarter_sale.b_sale_money = 0 THEN
0 ELSE last_quarter_sale.b_sale_money
END last_sale_money,
CASE
WHEN last_quarter_sale.b_sale_money IS NULL
OR last_quarter_sale.b_sale_money = 0 THEN
0 ELSE CONVERT ( ( ( now_quarter_sale.a_sale_money - last_quarter_sale.b_sale_money ) / last_quarter_sale.b_sale_money ) * 100, DECIMAL ( 10, 2 ) )
END last_quarter_ratio
FROM
(
SELECT
date_format( a.create_time, ‘%Y‘ ) AS now_year,
QUARTER ( a.create_time ) a_quarter,
CONCAT( YEAR ( a.create_time ), QUARTER ( a.create_time ) ) now_quarter,
sum( a.out_stock_money ) AS a_out_money,
sum( a.freight_money ) AS a_fre_money,
CONVERT ( sum( a.out_stock_money ) - sum( a.freight_money ), DECIMAL ( 10, 2 ) ) AS a_sale_money
FROM
food_orders_info a
WHERE
a.state = 1
AND a.order_state = 60
GROUP BY
CONCAT( YEAR ( a.create_time ), QUARTER ( a.create_time )),date_format( a.create_time, ‘%Y‘ ), QUARTER ( a.create_time )
) now_quarter_sale
LEFT JOIN (
SELECT
CONCAT( YEAR ( b.create_time ), QUARTER ( DATE_ADD( b.create_time, INTERVAL 1 QUARTER ) ) ) AS last_quarter,
sum( b.out_stock_money ) AS b_out_money,
sum( b.freight_money ) AS b_fre_money,
CONVERT ( sum( b.out_stock_money ) - sum( b.freight_money ), DECIMAL ( 10, 2 ) ) AS b_sale_money
FROM
food_orders_info b
WHERE
b.state = 1
AND b.order_state = 60
GROUP BY
CONCAT( YEAR ( b.create_time ), QUARTER ( DATE_ADD( b.create_time, INTERVAL 1 QUARTER ) ) )
) last_quarter_sale ON now_quarter_sale.now_quarter = last_quarter_sale.last_quarter
最终 ，统计当前季度、上一季度、以及去年同一季度的环比、同比
SELECT
now_quarter_sale.now_year,
now_quarter_sale.a_quarter,
now_quarter_sale.now_quarter,
CASE
WHEN now_quarter_sale.a_sale_money IS NULL
OR now_quarter_sale.a_sale_money = 0 THEN
0 ELSE now_quarter_sale.a_sale_money
END this_sale_money,
CASE
WHEN last_quarter_sale.b_sale_money IS NULL
OR last_quarter_sale.b_sale_money = 0 THEN
0 ELSE last_quarter_sale.b_sale_money
END last_sale_money,
CASE
WHEN last_quarter_sale.b_sale_money IS NULL
OR last_quarter_sale.b_sale_money = 0 THEN
0 ELSE CONVERT ( ( ( now_quarter_sale.a_sale_money - last_quarter_sale.b_sale_money ) / last_quarter_sale.b_sale_money ) * 100, DECIMAL ( 10, 2 ) )
END last_quarter_ratio ,
CASE
WHEN last_year_quarter_sale.c_sale_money IS NULL
OR last_year_quarter_sale.c_sale_money = 0 THEN
0 ELSE last_year_quarter_sale.c_sale_money
END last_year_money,
CASE
WHEN last_year_quarter_sale.c_sale_money IS NULL
OR last_year_quarter_sale.c_sale_money = 0 THEN
0 ELSE CONVERT ( ( ( now_quarter_sale.a_sale_money - last_year_quarter_sale.c_sale_money ) / last_year_quarter_sale.c_sale_money ) * 100, DECIMAL ( 10, 2 ) )
END last_year_ratio
FROM
(
SELECT
date_format( a.create_time, ‘%Y‘ ) AS now_year,
QUARTER ( a.create_time ) a_quarter,
CONCAT( YEAR ( a.create_time ), QUARTER ( a.create_time ) ) now_quarter,
sum( a.out_stock_money ) AS a_out_money,
sum( a.freight_money ) AS a_fre_money,
CONVERT ( sum( a.out_stock_money ) - sum( a.freight_money ), DECIMAL ( 10, 2 ) ) AS a_sale_money
FROM
food_orders_info a
WHERE
a.state = 1
AND a.order_state = 60
GROUP BY
CONCAT( YEAR ( a.create_time ), QUARTER ( a.create_time )),date_format( a.create_time, ‘%Y‘ ), QUARTER ( a.create_time )
) now_quarter_sale
LEFT JOIN (
SELECT
CONCAT( YEAR ( b.create_time ), QUARTER ( DATE_ADD( b.create_time, INTERVAL 1 QUARTER ) ) ) AS last_quarter,
sum( b.out_stock_money ) AS b_out_money,
sum( b.freight_money ) AS b_fre_money,
CONVERT ( sum( b.out_stock_money ) - sum( b.freight_money ), DECIMAL ( 10, 2 ) ) AS b_sale_money
FROM
food_orders_info b
WHERE
b.state = 1
AND b.order_state = 60
GROUP BY
CONCAT( YEAR ( b.create_time ), QUARTER ( DATE_ADD( b.create_time, INTERVAL 1 QUARTER ) ) )
) last_quarter_sale ON now_quarter_sale.now_quarter = last_quarter_sale.last_quarter
LEFT JOIN
(
SELECT
CONCAT(YEAR( DATE_ADD( c.create_time, INTERVAL 1 year )),QUARTER(c.create_time)) as last_year_quarter,
sum( c.out_stock_money ) AS c_out_money,
sum( c.freight_money ) AS c_fre_money,
CONVERT ( sum( c.out_stock_money ) - sum( c.freight_money ), DECIMAL ( 10, 2 ) ) AS c_sale_money
FROM
food_orders_info c
WHERE
c.state = 1
AND c.order_state = 60
GROUP BY
CONCAT(YEAR( DATE_ADD( c.create_time, INTERVAL 1 year )),QUARTER(c.create_time))
)last_year_quarter_sale on now_quarter_sale.now_quarter=last_year_quarter_sale.last_year_quarter
last_quarter_ratio = ((this_sale_money-last_sale_money)/last_sale_money)*100%
last_year_ratio = ((this_sale_money-last_year_money)/last_year_money)*100%
界面实现效果：
原文：https://www.cnblogs.com/lonelyxmas/p/12325984.html