背景:
最近在做报表统计,因为查不同的订单分布在不同的表里面,不同的表订单状态也不一样,所以用union all 做连接的时候要区分数据是来自那张表的,就是在字段的地方构造一个字段用于记录表名称。
(SELECT EMAIL,'order' as order_table from `order` WHERE STAMP BETWEEN '2016-01-01' AND '2019-05-31')
union all
(SELECT EMAIL,'cdkey_order' as order_table from `cdkey_order` WHERE STAMP BETWEEN '2016-01-01' AND '2019-05-31')
union all
(SELECT EMAIL,'lvlguide_order' as order_table from `lvlguide_order` WHERE STAMP BETWEEN '2016-01-01' AND '2019-05-31')
例子:union all 数据表来源order表则在select 字段时候增加:'order' as order_table from xxxx 那么这个字段就可以标记来源那张表
同理:union all数据表来自cdk表,则在select时候增加:'cdkey' as order_table from xxxx
再来个例子:
(select name,uid,'user' as table_name from user)
union all
(select name,uid,'customer' as table_name from customer)