这个SQL语句怎么写效率最好？

Question

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'东城区')

上边是测试数据，请问怎么写效率最高？谢谢！

Answer 1

--这样应该是效率最高的了 只有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

Answer 2

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

Answer 3

写得很好，可是我看不懂，那个with和nolock是干嘛用的啊？

Answer 4

@hexllo: WITH(NOLOCK)  读取表的时候不加锁，可提高性能

Answer 5

基本上就是子查询+inner join 吧。更高级的办法没想到。

Answer 6

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