1.
SELECT
t1.now_year_order AS 年份,
t1.now_month_order 月份,
order_time,
CASE
WHEN now_month_order_num IS NULL
OR now_month_order_num = 0 THEN
0 ELSE now_month_order_num
END 本月订单量,
CASE
WHEN last_month_order_num IS NULL
OR last_month_order_num = 0 THEN
0 ELSE last_month_order_num
END 去年同期订单量
,CASE
WHEN last_month_order_num IS NULL
OR last_month_order_num = 0 THEN
0 ELSE last_month_order_num2
END 上个月订单量
FROM
(
SELECT
DATE_FORMAT( a.order_time, ‘%Y‘ ) AS now_year_order,
DATE_FORMAT( a.order_time, ‘%Y-%m‘ ) AS now_month_order,
sum( a.order_num ) AS now_month_order_num
FROM
saleorder AS a
GROUP BY
now_month_order
) t1
LEFT JOIN (
SELECT
DATE_FORMAT( DATE_ADD( b.order_time, INTERVAL 1 year ), ‘%Y-%m‘ )
AS now_month_order,
sum( b.order_num ) AS last_month_order_num
FROM
saleorder b
GROUP BY
now_month_order
) AS t2 ON t1.now_month_order = t2.now_month_order LEFT JOIN (
SELECT DATE_FORMAT( c.order_time, ‘%Y-%m‘ ) order_time,
DATE_FORMAT( DATE_ADD( c.order_time, INTERVAL 1 MONTH ), ‘%Y-%m‘ )
AS now_month_order,
sum( c.order_num ) AS last_month_order_num2
FROM
saleorder c
GROUP BY
now_month_order
) AS t3 ON t1.now_month_order = t3.now_month_order;