• MySQL_西安11月销售昨日未上架的产品_20161212


    #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.应收日
    
  • 相关阅读:
    打造属于自己的谷歌Maps版博客公告【演示+源码】
    CentOS 更改默认启动顺序
    VC的控制台程序,如何获取输入参数和环境变量
    如何同时提供ANSI和UNICODE函数
    VC工程需要注意字符集问题
    升级firefox到最新版
    VC工程需要注意字符集问题
    如何同时提供ANSI和UNICODE函数
    使C++ Builder编译程度独立运行
    安装谷歌浏览器
  • 原文地址:https://www.cnblogs.com/Mr-Cxy/p/6166026.html
Copyright © 2020-2023  润新知