这个SQL语句怎么写效率最好？
0
[已解决问题]
解决于 2012-08-21 10:47
A表中有4个字段，出生省份ID，出生城市ID，居住省分ID，居住城市ID
B有省分ID,省分名称
C表有城市ID，省分名称
现在要查出A表中的所有数据，并将各ID换成具体的省，市名称。
create table a(birthProvinceID int,brithCityID int ,liveProvinceID int,liveCityID int)
create table b(provinceID int ,provinceName nvarchar(10))
create table c(cityID int,cityName nvarchar(10))
insert a values(1,1,2,2)
insert b values(1,N'云南')
insert b values(2,N'北京')
insert c values(1,N'昆明')
insert c values(2,N'东城区')
上边是测试数据，请问怎么写效率最高？谢谢！
最佳答案
0
--这样应该是效率最高的了 只有left join 没有子查询 select a.*, b1.provinceName as birthProvince, c1.cityName as birthCity, b2.provinceName as liveProvince, c2.cityName as liveCity from a left join b b1 on a.birthProvinceID = b1.provinceID left join c c1 on a.birthCityID = c1.cityID left join b b2 on a.liveProvinceID = b2.provinceID left join c c2 on a.liveCityID = c2.cityID
奖励园豆：5
其他回答(3)
0
SET STATISTICS IO ON ;
WITH tab1
AS ( SELECT a.* ,
b.provinceName AS BirthProvinceName
FROM dbo.a WITH(NOLOCK)
INNER JOIN dbo.b WITH(NOLOCK) ON a.birthProvinceID = b.provinceID
),
tab2
AS ( SELECT BirthProvinceName ,
liveCityID ,
tab1.brithCityID ,
B.provinceName ,
b.provinceName AS LiveProvinceName
FROM tab1 WITH(NOLOCK)
INNER JOIN dbo.b WITH(NOLOCK) ON tab1.liveProvinceID = b.provinceID
),
tab3
AS ( SELECT tab2.* ,
c.cityName AS BirthCityName
FROM tab2 WITH(NOLOCK)
INNER JOIN dbo.c WITH(NOLOCK) ON tab2.brithCityID = c.cityID
)
SELECT BirthProvinceName ,
BirthCityName ,
LiveProvinceName ,
c.cityName AS LiveCityName
FROM TAB3 WITH(NOLOCK)
INNER JOIN c WITH(NOLOCK) ON tab3.liveCityID = c.cityID
SET STATISTICS IO OFF
写得很好，可是我看不懂，那个with和nolock是干嘛用的啊？
@hexllo: WITH(NOLOCK) 读取表的时候不加锁，可提高性能
0
基本上就是子查询+inner join 吧。更高级的办法没想到。
0
select B.provinceName as BirthProvince , C.cityName as BirthCity ,LiveProvince, LiveCity
from B, C, select birthProvinceID ,brithCityID, provinceName as LiveProvince , cityName as LiveCity
from A, B, C
where A.liveProvinceID=B. provinceName and A.liveCityID=C.cityName
M
where M.birthProvinceID=B. provinceName and M.birthCityID=C.cityName
