mysql_union all 纵向合并建表_20170123
2024-08-25 02:21:18
年前事情比较多,博客不能每天更新了。
1、union all 纵向建表和left join 横向建表的数据结构区别 先贴代码 后面再补充
(#销售确认额
SELECT '05收货销售额' AS 标识,城市,餐馆ID,销售员,订单号 AS 订单ID,订单日期 AS 订单时间,SKUID,NULL AS 相应ID,NULL AS 相应名称,类型,单位,销量 AS 数量,'A收货确认额' AS 类型1,成本额 AS 相应成本,销售额 AS 金额,毛利额 AS 毛利,应收日 AS 收货时间
FROM `a005_account`
WHERE 销售额<>0 AND 订单日期>='2017-01-01' AND 订单日期<CURRENT_DATE
)
UNION ALL
(#销售成本额
SELECT '05收货成本额' AS 标识,城市,餐馆ID,销售员,订单号 AS 订单ID,订单日期 AS 订单时间,SKUID,NULL AS 相应ID,NULL AS 相应名称,类型,单位,销量 AS 数量,'B收货成本额' AS 类型1,成本额 AS 相应成本,0-成本额 AS 金额,0 AS 毛利,应收日 AS 收货时间
FROM `a005_account`
WHERE 销售额<>0 AND 订单日期>='2017-01-01' AND 订单日期<CURRENT_DATE
)
UNION ALL
(#赠品额
SELECT '05收货赠品' AS 标识,城市,餐馆ID,销售员,订单号 AS 订单ID,订单日期 AS 订单时间,SKUID,NULL AS 相应ID,NULL AS 相应名称,类型,单位,销量 AS 数量,'C赠品成本' AS 类型1,成本额 AS 相应成本,0-成本额 AS 金额,0-成本额 AS 毛利,应收日 AS 收货时间
FROM `a005_account`
WHERE 销售额=0 AND 订单日期>='2017-01-01' AND 订单日期<CURRENT_DATE
)
UNION ALL
(#优惠券
SELECT '16优惠券' AS 标识,c1.城市,c1.餐馆ID,c2.销售员,c1.订单号 AS 订单ID,c1.下单时间 AS 订单时间,NULL AS SKUID,c1.优惠券ID AS 相应ID,c1.优惠券名称 AS 相应名称,'赠券' AS 类型,'张' AS 单位,1 AS 数量,'D优惠券成本' AS 类型1,c1.优惠券金额 AS 相应成本,0-c1.优惠券金额 AS 金额,0-c1.优惠券金额 AS 毛利,c1.收货时间
FROM `a016_order_customercoupon_xref` AS c1
LEFT JOIN `a003_order` AS c2 ON c1.订单号=c2.订单ID
WHERE c1.下单时间>='2017-01-01' AND c1.下单时间<CURRENT_DATE
)
UNION ALL
(#满减券
SELECT '36满减' AS 标识,d1.城市,d1.餐馆ID,d2.销售员,d1.订单ID,d1.下单时间 AS 订单时间,NULL AS SKUID,d1.活动ID AS 相应ID,d1.活动描述 AS 相应名称,'满减' AS 类型,'张' AS 单位,1 AS 数量,'E满减券成本' AS 类型1,d1.满减金额 AS 相应成本,0-d1.满减金额 AS 金额,0-d1.满减金额 AS 毛利,d1.收货时间
FROM `a036_order_promotion_xref` AS d1
LEFT JOIN `a003_order` AS d2 ON d1.订单ID=d2.订单ID
WHERE d1.下单时间>='2017-01-01' AND d1.下单时间<CURRENT_DATE
) UNION ALL
(#运费成本
SELECT '12运费' AS 标识,d1.城市,d1.餐馆ID,d1.销售员,d1.订单ID ,d1.订单日期 AS 订单时间,NULL AS SKUID,d1.订单包id AS 相应ID,线路 AS 相应名称,'运费' AS 类型,'件' AS 单位,件数 AS 数量,'F运费成本' AS 类型1,ROUND(d1.费用*d1.金额/d3.金额,4) AS 相应成本,ROUND(0-d1.费用*d1.金额/d3.金额,4) AS 金额,ROUND(0-d1.费用*d1.金额/d3.金额,4) AS 毛利,d2.应收日 AS 收货时间
FROM a012_cgwy_order_group2 AS d1
LEFT JOIN `a005_account` AS d2 ON d1.订单ID=d2.订单号
LEFT JOIN (
SELECT 订单包ID,SUM(金额) AS 金额
FROM `a012_cgwy_order_group2` AS d1
GROUP BY 订单包ID
) AS d3 ON d1.订单包ID=d3.订单包ID
WHERE d1.订单日期>='2017-01-01' AND d1.订单日期<CURRENT_DATE
GROUP BY d1.订单ID
)
最新文章
- C#委托使用详解（Delegates）
- AngularJS下拉列表select在option动态变化之后多出了一个错误项的问题
- WinForm 进程、线程、TreeView递归加载、发送邮件--2016年12月13日
- Beta阶段第五次Scrum Meeting
- CSS之伪类
- virtualbox虚拟机中的centos与macos共享文件夹
- myeclipse 控制台打印空指针 ，黏贴控制台sql到plsql有结果集，异常处理
- 我的css笔记
- an alternative to symmetric multiprocessing
- pyspark 中启动 jupyter notebook
- 关于Linux的时间与时区
- Arduino 板子 COM 接口找不到设备
- QNX系统-关于delay函数与sleep函数的区别
- Java IO学习--(四)网络
- CCF CSP 201412-1 门禁系统
- ASP.NET MVC中，动态处理页面静态化 【转载】
- Validation.Add();Excel
- Oracle数据库---用户与角色
- pycharm 中按照文档引包方式，引包错误
- TStringList 复制 赋值。