需求：杭州拱墅区、西湖区近9-11月销售过的产品
这个SQL如果对数据表熟悉的话可以一步到位,为了看的更清楚,时间间隔在3个月产品数量也不是很多,采取先找明细再进行汇总。
一、第一种写法 分步骤的写SQL 逻辑清晰
1、首先先观察区块的字段
SELECT 区块 FROM `a001_resterant` WHERE 城市='杭州' GROUP BY 区块
可以看到 西湖区是划成了5个分区 因此需要对这5个分区改成统一的分区利用case when 条件判断生成新字段 分区
2、明细。由于用户区块是固定的,先找到每个用户每个月每个产品的销售情况。
SELECT a1.城市,a1.用户ID,DATE_FORMAT(订单日期,"%Y%m") AS 年月,a2.区块,CASE WHEN a2.区块='拱墅区' THEN '拱墅区' WHEN a2.区块 LIKE '西湖%' THEN '西湖区' ELSE NULL END AS 分区,SKUID,SKU名称,SUM(金额) AS 金额
FROM `a003_order` AS a1
LEFT JOIN `a001_resterant` AS a2 ON a1.用户ID=a2.用户ID
WHERE a1.城市='杭州' AND 金额>0 AND (a2.区块='拱墅区' OR a2.区块 LIKE '西湖%') AND 订单日期>='2016-09-01' AND 订单日期<CURRENT_DATE
GROUP BY a1.用户ID,DATE_FORMAT(订单日期,"%Y%m"),SKUID
3、汇总。此时由于嵌套表已把西湖五个区划成统一的西湖区，因此嵌套表里的分区字段进行group by
SELECT 城市,分区,b.商品分类一级,b.商品分类二级,b.商品分类三级,a.SKUID,a.SKU名称,SUM(金额) AS 订单额,SUM(IF(年月=201609,金额,NULL)) AS 9月金额,SUM(IF(年月=201610,金额,NULL)) AS 10月金额,SUM(IF(年月=201611,金额,NULL)) AS 11月金额
FROM (#明细 每个用户所在区块的近三个月销售的SKU
SELECT a1.城市,a1.用户ID,DATE_FORMAT(订单日期,"%Y%m") AS 年月,a2.区块,CASE WHEN a2.区块='拱墅区' THEN '拱墅区' WHEN a2.区块 LIKE '西湖%' THEN '西湖区' ELSE NULL END AS 分区,SKUID,SKU名称,SUM(金额) AS 金额
FROM `a003_order` AS a1
LEFT JOIN `a001_resterant` AS a2 ON a1.用户ID=a2.用户ID
WHERE a1.城市='杭州' AND 金额>0 AND (a2.区块='拱墅区' OR a2.区块 LIKE '西湖%') AND 订单日期>='2016-09-01' AND 订单日期<CURRENT_DATE
GROUP BY a1.用户ID,DATE_FORMAT(订单日期,"%Y%m"),SKUID
) AS a
LEFT JOIN `a002_sku` AS b ON a.SKUID=b.SKUID
GROUP BY 分区,a.SKUID
ORDER BY 分区,b.商品分类一级,b.商品分类二级,b.商品分类三级,SUM(金额) DESC
二、第二种写法 一步到位
SELECT a.城市,CASE WHEN c.区块='拱墅区' THEN '拱墅区' WHEN c.区块 LIKE '西湖%' THEN '西湖区' ELSE NULL END AS 分区,b.商品分类一级,b.商品分类二级,b.商品分类三级,a.SKUID,a.SKU名称,SUM(金额) AS 订单额,SUM(IF(DATE_FORMAT(订单日期,"%Y%m")=201609,金额,NULL)) AS 9月金额,SUM(IF(DATE_FORMAT(订单日期,"%Y%m")=201610,金额,NULL)) AS 10月金额,SUM(IF(DATE_FORMAT(订单日期,"%Y%m")=201611,金额,NULL)) AS 11月金额
FROM `a003_order` AS a
LEFT JOIN `a002_sku` AS b ON a.SKUID=b.SKUID
LEFT JOIN `a001_resterant` AS c ON a.餐馆ID=c.餐馆ID
WHERE a.城市='杭州' AND 金额>0 AND (c.区块='拱墅区' OR c.区块 LIKE '西湖%') AND 订单日期>='2016-09-01' AND 订单日期<CURRENT_DATE
GROUP BY 分区,a.SKUID
ORDER BY 分区,b.商品分类一级,b.商品分类二级,b.商品分类三级,SUM(金额) DESC