MySQL_关于用嵌套表计算的可以不用 20161205

计算求和类的指标,其实用不到嵌套表，比如计算各城市产品分类的订单额。

如果要计算不重复的指标比如一个用户一天下了多个订单用这样的表计算一天有多少用户下单这个用户肯定是去重的下多个订单也应该视为一个用户

这个用嵌套表主要是为了逻辑理解清楚一步步来,如果对数据库表很熟悉的话可以省去嵌套表的步骤

1、嵌套表

##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

　2、不用嵌套

##C024_02 上周品类销售收入
SELECT  CONCAT(a3.分类ID,'-',a2.产品分类) 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 `a005_account` AS a1
LEFT JOIN `a002_产品` AS a2 ON a1.产品ID=a2.产品ID
LEFT JOIN `a000_cate` AS a3 ON a2.产品分类=a3.产品分类
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.产品分类
ORDER BY a3.分类ID