select orderId
, productId
, productName
, userId
, productType
, payMoney
, payTime
, orderStatus
, vipPrice
, orderType
,discountsPrice
,create_time
from ((select a.id as orderId
, a.product_id as productId
, a.user_id as userId
,a.product_name as productName
, a.product_type as productType
, a.pay_money as payMoney
,a.pay_time as payTime
, a.order_status as orderStatus
,b.vip_price as vipPrice
,a.discounts_price as discountsPrice
, 0 as orderType
,a.create_time
from 表1 a left join 表2 b on a.product_id = b.id
<where>
1=1
</where>
) union all
( select id
, user_id as userId
, null as productId
, product_name as productName
, customer_type as productType
, a.account_money as payMoney
,a.account_time as payTime
,'SUCCESS' as orderStatus
,null as vipPrice
,0 as discountsPrice
, '1' as orderType
,create_time
from 表3 a
<where>
1=1
</where>
)
) t
order by create_time desc
注:其中select后边字段前后顺序要相同