#C034西安11月销售昨日未上架的产品
SELECT 城市,a.订单日期,a.客户数,a.订单数,b.产品数,a.金额,c.销售确认额,c.毛利额,c.毛利率 FROM ( SELECT 城市,订单日期,COUNT(用户ID) AS 客户数,SUM(订单数) AS 订单数,SUM(金额) AS 金额 FROM ( SELECT 城市,订单日期,用户ID,COUNT(订单ID) AS 订单数,SUM(金额) AS 金额 FROM ( SELECT 城市,DATE(订单日期) AS 订单日期,用户ID,订单ID,SUM(金额) AS 金额 FROM `a003_order` AS a1 WHERE 城市='西安' AND 金额>0 AND 订单日期>=DATE_ADD(CURRENT_DATE,INTERVAL -15 DAY) AND 订单日期<CURRENT_DATE GROUP BY 订单ID ) AS a2 GROUP BY 订单日期,用户ID ) AS a3 GROUP BY 订单日期 ) AS a LEFT JOIN (#产品数 SELECT 订单日期,COUNT(产品ID) AS 产品数,SUM(金额) AS 金额 FROM ( SELECT DATE(订单日期) AS 订单日期,产品ID,SUM(金额) AS 金额 FROM `a003_order` WHERE 城市='西安' AND 金额>0 AND 订单日期>=DATE_ADD(CURRENT_DATE,INTERVAL -15 DAY) AND 订单日期<CURRENT_DATE GROUP BY DATE(订单日期),产品ID ) AS b1 GROUP BY 订单日期 ) AS b ON a.订单日期=b.订单日期 LEFT JOIN (#每天毛利 SELECT DATE(应收日) AS 应收日,SUM(销售额) AS 销售确认额,SUM(毛利额) AS 毛利额,SUM(毛利额)/SUM(销售额) AS 毛利率 FROM `a005_account` WHERE 城市='西安' AND 应收日>=DATE_ADD(CURRENT_DATE,INTERVAL -15 DAY) AND 应收日<CURRENT_DATE GROUP BY DATE(应收日) ) AS c ON a.订单日期=c.应收日