首页新闻找找看学习计划

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

上边是测试数据,请问怎么写效率最高?谢谢!

hexllo的主页 hexllo | 菜鸟二级 | 园豆:405
提问于:2012-04-05 16:11
< >
分享
最佳答案
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
gunsmoke | 老鸟四级 |园豆:3592 | 2012-07-12 08:17
其他回答(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
哇~怪兽 | 园豆:603 (小虾三级) | 2012-04-05 16:32

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

支持(0) 反对(0) hexllo | 园豆:405 (菜鸟二级) | 2012-04-05 16:46

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

支持(0) 反对(0) 哇~怪兽 | 园豆:603 (小虾三级) | 2012-04-05 17:37
0

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

迷路中的路人甲 | 园豆:93 (初学一级) | 2012-04-05 16:52
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   

10283 | 园豆:221 (菜鸟二级) | 2012-04-05 21:49
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册