MySQL_财务统计各产品品类各城市上周收入毛利表_20161202

#销售总收入汇总
SELECT ‘销售总收入‘ AS 销售总收入
,SUM(IF(城市="城市A",销售确认额,NULL)) AS 城市A,SUM(IF(城市="城市B",销售确认额,NULL)) AS 城市B,SUM(IF(城市="城市C",销售确认额,NULL)) AS 城市C
,SUM(IF(城市="城市D",销售确认额,NULL)) AS 城市D,SUM(IF(城市="城市E",销售确认额,NULL)) AS 城市E
FROM (#上周一到本周一
	SELECT 城市,SUM(销售额) AS 销售确认额
	FROM `a005_account`
	WHERE 应收日>=DATE_ADD(DATE(DATE_ADD(CURRENT_DATE,INTERVAL -WEEKDAY(CURRENT_DATE) DAY)),INTERVAL -1 WEEK) AND 应收日<DATE(DATE_ADD(CURRENT_DATE,INTERVAL -WEEKDAY(CURRENT_DATE) DAY))
	GROUP BY 城市
) AS a

2、#C024_02 上周品类销售收入

##C024_02 上周品类销售收入
SELECT  CONCAT(b.分类ID,‘-‘,a.产品分类) AS 商品分类
,SUM(IF(城市="城市A",销售确认额,NULL)) AS 城市A,SUM(IF(城市="城市B",销售确认额,NULL)) AS 城市B,SUM(IF(城市="城市C",销售确认额,NULL)) AS 城市C
,SUM(IF(城市="城市D",销售确认额,NULL)) AS 城市D,SUM(IF(城市="城市E",销售确认额,NULL)) AS 城市E
FROM (#上周一到本周一
	SELECT 城市,a2.产品分类,SUM(销售额) AS 销售确认额
	FROM `a005_account` AS a1
	LEFT JOIN `a002_产品` AS a2 ON a1.产品ID=a2.产品ID
	WHERE 应收日>=DATE_ADD(DATE(DATE_ADD(CURRENT_DATE,INTERVAL -WEEKDAY(CURRENT_DATE) DAY)),INTERVAL -1 WEEK) AND 应收日<DATE(DATE_ADD(CURRENT_DATE,INTERVAL -WEEKDAY(CURRENT_DATE) DAY))
	GROUP BY 城市,a2.产品分类
) AS a
LEFT JOIN `a000_cate` AS b ON a.产品分类=b.产品分类
GROUP BY a.产品分类
ORDER BY b.分类ID

3、#C024_03成本额汇总  #C024_04品类成本额 及毛利额汇总 品类毛利额 都和上面是一样的仅仅是sum(字段)不同可以 省略代码

4、#C024_09 上周优惠券金额

#C024_09 上周优惠券金额
SELECT  ‘优惠券‘ AS 商品分类
,SUM(IF(城市="城市A",优惠券金额,NULL)) AS 城市A,SUM(IF(城市="城市B",优惠券金额,NULL)) AS 城市B,SUM(IF(城市="城市C",优惠券金额,NULL)) AS 城市C
,SUM(IF(城市="城市D",优惠券金额,NULL)) AS 城市D,SUM(IF(城市="城市E",优惠券金额,NULL)) AS 城市EFROM (#上周一到本周一
	SELECT 城市,SUM(优惠券金额) AS 优惠券金额
	FROM `a016_order_customercoupon_xref` 
	WHERE 使用时间>=DATE_ADD(DATE(DATE_ADD(CURRENT_DATE,INTERVAL -WEEKDAY(CURRENT_DATE) DAY)),INTERVAL -1 WEEK) AND 使用时间<DATE(DATE_ADD(CURRENT_DATE,INTERVAL -WEEKDAY(CURRENT_DATE) DAY))
	GROUP BY 城市
) AS a 

5、设置excel模板 kettle转换 作业

6、数据展现 真实数据已处理 下面数据和实际逻辑数值不等  

MySQL_财务统计各产品品类各城市上周收入毛利表_20161202

标签：需要   concat   统计   nbsp   join   收入   interval   str   tle