SQLsummary/报表/订单来源明细.sql

SELECT
	T1.`期数`,
	T5.`DS系统` AS 系统,
	T3.`商城复消`+T4.`商城蓝区` AS 商城,
	T2.`线下工作室`,
	T1.`线下其他`,
	T1.`线下其他`+T2.`线下工作室`+T3.`商城复消`+T4.`商城蓝区`+T5.`DS系统` AS 总计
FROM
(SELECT 
	O.`期数`,
	SUM(O.`商品金额`) AS 线下其他
FROM
	zr_order_vw O
	LEFT JOIN `订单来源` L ON L.`订单号` = O.`订单号`
	WHERE L.`订单来源` = '线下其他' AND O.`期数` >= {{PERIOD_NUM_MIN}} AND O.`期数` <= {{PERIOD_NUM_MAX}} GROUP BY O.`期数`) T1
	LEFT JOIN 
	(SELECT 
	O.`期数`,
	SUM(O.`商品金额`) AS 线下工作室
FROM
	zr_order_vw O
	LEFT JOIN `订单来源` L ON L.`订单号` = O.`订单号`
	WHERE L.`订单来源` = '线下工作室' AND O.`期数` >= {{PERIOD_NUM_MIN}} AND O.`期数` <= {{PERIOD_NUM_MAX}} GROUP BY O.`期数`) T2 ON T2.`期数` = T1.`期数`
	
	LEFT JOIN 
	(SELECT 
	O.`期数`,
	SUM(O.`商品金额`) AS 商城复消
FROM
	zr_order_vw O
	LEFT JOIN `订单来源` L ON L.`订单号` = O.`订单号`
	WHERE L.`订单来源` = '商城复消' AND O.`期数` >= {{PERIOD_NUM_MIN}} AND O.`期数` <= {{PERIOD_NUM_MAX}} GROUP BY O.`期数`) T3 ON T3.`期数` = T2.`期数`
	
	LEFT JOIN 
 (SELECT 
  O.`期数`,
	SUM(O.`商品金额`) AS 商城蓝区
FROM
	zr_order_vw O
	LEFT JOIN `订单来源` L ON L.`订单号` = O.`订单号`
	WHERE L.`订单来源` = '商城蓝区' AND O.`期数` >= {{PERIOD_NUM_MIN}} AND O.`期数` <= {{PERIOD_NUM_MAX}} GROUP BY O.`期数`) T4 ON T4.`期数` = T3.`期数`
	LEFT JOIN 
	
	(SELECT 
	O.`期数`,
	SUM(O.`商品金额`) AS DS系统
FROM
	zr_order_vw O
	LEFT JOIN `订单来源` L ON L.`订单号` = O.`订单号`
	WHERE L.`订单来源` = 'DS系统' AND O.`期数` >= {{PERIOD_NUM_MIN}} AND O.`期数` <= {{PERIOD_NUM_MAX}} GROUP BY O.`期数`) T5 ON T5.`期数` = T4.`期数`
	ORDER BY T1.`期数` ASC