50分 求一SQL语句 先对先得

[wwonion]

SELECT *,
(SELECT city
FROM province_city_CN
WHERE id = dealer_VIEW.province) AS 省份
,
(SELECT city
FROM province_city_CN
WHERE id = dealer_VIEW.city) AS 城市

FROM dealer_VIEW

能否优化下??

[子陌红尘]

SELECT
a.*,
b.city AS 省份,
c.city AS 城市
FROM
dealer_VIEW a,
province_city_CN b,
province_city_CN c
where
a.province=b.id and a.city=c.id

[冷箫轻笛]

select *,t2.city as 省份,t3.city as 城市
from dealer_VIEW t1 left join province_city_CN t2 on t1.province = t2.id
left join province_city_CN t3 on t1.city = t3.id

[hhhdyj]

select dv.*
from dealer_VIEW dv
inner join province_city_CN p
on p.id = dv.province
inner join city c
on c.id = dv.city

[liuyinbo0109]

SELECT *,
city as 省份,
city as 城市
FROM dealer_VIEW

是不是这样？

[wwonion]

请问还有别的思路吗?

谢谢大家的帮助,我认为 libin_ftsafe(子陌红尘：当libin告别ftsafe) 是最好的方法了
但速度提升并不明显.

[子陌红尘]

是否已经在关联字段上建立索引？

[wwonion]

ID,province,CITY 都是INT

province_city_CN 的ID 是索引,其他的字段没索引

[allright_flash]

select *,province_city_CN.city as 省份,province_city_CN.city as 城市
from dealer_VIEW
left join province_city_CN on dealer_VIEW.province = province_city_CN.id
left join province_city_CN on dealer_VIEW.city = province_city_CN.id