[size=xx-small]
select distinct fr.name fname from tms_shipment_carrying_detail t
left join tms_shipment s on s.id=t.shipment_id
left join tms_legs f_leg on f_leg.shipment_id=s.id
left join tms_trans_location tr on tr.id=f_leg.tolocation_id
left join tms_trans_location fr on fr.id=f_leg.fromlocation_id
[color=green]order by[/color] [color=orange]tr.name[/color];
ORA-01791: 不是 SELECTed 表达式!
原因:select distinct 和order by一起使用的时候,order by中必须是常量或者select列表中出现的表达式。(单表时没有这种情况)
改为:
select distinct fr.name fname[color=red],tr.name tname [/color]from tms_shipment_carrying_detail t
left join tms_shipment s on s.id=t.shipment_id
left join tms_legs f_leg on f_leg.shipment_id=s.id
left join tms_trans_location tr on tr.id=f_leg.tolocation_id
left join tms_trans_location fr on fr.id=f_leg.fromlocation_id
[color=indigo]order by[/color] [color=red]tr.name[/color] ; [/size]
select distinct fr.name fname from tms_shipment_carrying_detail t
left join tms_shipment s on s.id=t.shipment_id
left join tms_legs f_leg on f_leg.shipment_id=s.id
left join tms_trans_location tr on tr.id=f_leg.tolocation_id
left join tms_trans_location fr on fr.id=f_leg.fromlocation_id
[color=green]order by[/color] [color=orange]tr.name[/color];
ORA-01791: 不是 SELECTed 表达式!
原因:select distinct 和order by一起使用的时候,order by中必须是常量或者select列表中出现的表达式。(单表时没有这种情况)
改为:
select distinct fr.name fname[color=red],tr.name tname [/color]from tms_shipment_carrying_detail t
left join tms_shipment s on s.id=t.shipment_id
left join tms_legs f_leg on f_leg.shipment_id=s.id
left join tms_trans_location tr on tr.id=f_leg.tolocation_id
left join tms_trans_location fr on fr.id=f_leg.fromlocation_id
[color=indigo]order by[/color] [color=red]tr.name[/color] ; [/size]