一个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
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
表:tb_search
search_date,search_type,search_site,to_city,from_city
表:tb_city
id,iata_code
表:tb_core_meta_locale
name,lookup_key,language
示例数据:
tb_search:
2008-01-01,1,www.baidu.com,SHA,BJS
2008-01-01,1,www.google.com,SHA,BJS
tb_city
1,SHA
2,BJS
tb_core_meta_locale
上海,SHA,zh_CN
上海,BJS,zh_CN
想要查询出tb_search时出现以下结果
2008-01-01,1,www.baidu.com,上海,北京
2008-01-01,1,www.google.com,上海,北京
字符型字段里储存的也是数字
?
应该有数值类型的吧把创建表的语句直接贴出来吧
lookup_key 为 SHA, BJS
明显错了吧,应该是 iata_code = lookup_key 才对吧?
t1.search_type,
t1.search_site,
(select t2.name from tb_city t2 where t2.lookup_key = t1.to_city and t2.language = 'zh_CN') to_city,
(select t2.name from tb_city t2 where t2.lookup_key = t1.from_city and t2.language = 'zh_CN') from_city
from tb_search t1;