急求一sql,谢谢
一个sql:tb_search表中有from_city和to_city字段,不过都是存的城市的code,tb_city中存的城市的数据,tb_core_meta_locale表中存的每个城市的各种语言的名称,现使用
select a.iata_code,m.name from tb_city a,tb_core_meta_locale m where a.id = m.lookup_key and m.language = 'zh_CN'
可以查出城市的code和中文名称,怎么在查询tb_search时候,把to_city 和from_city 换成中文的?
现我用left join换了一个:
select s.search_date,s.search_site,s.search_type,s.search_count,a.name as from_city,to_city from tb_search s left join (
select a.iata_code,m.name from tb_city a,tb_core_meta_locale m where a.id = m.lookup_key and m.language = 'zh_CN'
) a on s.from_city=a.iata_code
可以,但是再用一个left join时候:
select * from (
select s.search_date,s.search_site,s.search_type,s.search_count,a.name as from_city,to_city from tb_search s left join (
select a.iata_code,m.name from tb_city a,tb_core_meta_locale m where a.id = m.lookup_key and m.language = 'zh_CN'
) a on s.from_city=a.iata_code
) aaa left join (
select a.iata_code,m.name from tb_city a,tb_core_meta_locale m where a.id = m.lookup_key and m.language = 'zh_CN'
) bbb on aaa.to_city=bbb.iata_code
就报错ORA-01722 无效数字。不知道怎么回事
,请高手指点,这种需求怎么写sql,我的思路是不是不对,这么写为什么出问题了呢?
另水区我也有提问,100分,解决了问题的大哥大姐,我一并给你分。
http://topic.csdn.net/u/20090903/17/ab3548cf-9143-4351-83bc-867fbb9fc50d.html?25767