这个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