我尝试了一个并集和并集,但我遇到了一个错误,错误地使用了并集。
select sr.shipper_city,
sr.consignee_site_city,
cast((avg(round((to_seconds(sr.delv_live_start)- to_seconds(sr.pick_depart_loaded))/(60*60*24),1))) as decimal) as TransitDays90
from cs.shipment_reporting sr
where customer_id = '15619'
and (pick_depart_loaded >(NOW() - INTERVAL 3 MONTH))
group by sr.shipper_city,sr.consignee_site_city
order by sr.shipper_city,sr.consignee_site_city
select sr.shipper_city,
sr.consignee_site_city,
(cast((avg(round((to_seconds(sr.delv_live_start)- to_seconds(sr.pick_depart_loaded))/(60*60*24),1))) as decimal)) as TransitDays30
from cs.shipment_reporting sr
where sr.customer_id = '15619' and (sr.pick_depart_loaded >(NOW() - INTERVAL 1 MONTH))
group by sr.shipper_city,sr.consignee_site_city
order by sr.shipper_city,sr.consignee_site_city
通过外部查询执行订单,尝试:
select shipper_city,consignee_site_city,TransitDays90
from (
select sr.shipper_city,
sr.consignee_site_city,
cast((avg(round((to_seconds(sr.delv_live_start)- to_seconds(sr.pick_depart_loaded))/(60*60*24),1))) as decimal) as TransitDays90
from cs.shipment_reporting sr
where customer_id = '15619'
and (pick_depart_loaded >(NOW() - INTERVAL 3 MONTH))
group by sr.shipper_city,sr.consignee_site_city
---- removed order by , it has no effect
UNION
select sr.shipper_city,
sr.consignee_site_city,
(cast((avg(round((to_seconds(sr.delv_live_start)- to_seconds(sr.pick_depart_loaded))/(60*60*24),1))) as decimal)) as TransitDays30
from cs.shipment_reporting sr
where sr.customer_id = '15619' and (sr.pick_depart_loaded >(NOW() - INTERVAL 1 MONTH))
group by sr.shipper_city,sr.consignee_site_city
) as t1
order by shipper_city , consignee_site_city ;
您的查询具有不同的列名:TransitDays90和TransitDays30。
根据您的需求,您可以将它们更改为相同的列,也可以在每个查询中添加NULL列,但要确保每个查询中的列顺序和列数相同