sql多表联查实例

sql多表联查实例

下面提供四款sql多表关联查询的实例，个个效率不一样。

select

*

from

order_info

as

a

,ivrlog4ivrdlvinst

as

b

where

(a.saleorder=b.ext1_skill

and

b.start_date=@date1

and

se_id='55'

and

b.ext1_skill!='')

and

convert(varchar(10),a.instime,112)=@date2

and

max(a.instime)

方法二

select

*

from

order_info

as

a

where

a.saleorder=(

select

b.ext1_skill

from

ivrlog4ivrdlvinst

as

b

where

b.start_date=@date1

and

se_id='55'

and

b.ext1_skill!='')

and

convert(varchar(10),max(a.instime),112)=@date2

方法三

declare

@date1

varchar(20),

@date2

varchar(20)

set

@date1='20100812'

set

@date2='2010-08-12'

select

*

from

order_info

as

a

where

a.saleorder=

(select

b.ext1_skill

from

ivrlog4ivrdlvinst

as

b

where

b.start_date=@date1

and

se_id='55'

and

b.ext1_skill!='')

and

convert(varchar(10),a.instime,112)=@date2

and

max(a.instime)

方法四

select

b.caller,

b.start_date,

b.start_time,

b.ext1_skill,

c.deliveryno,

c.destroyresult,

c.deliverydate,

c.deliverytime,

c.arrangetime,

c.driverphone,

c.drivermobile,

a.servicedate,

a.servicetime,

a.workertel

from

order_info

as

a

,ivrlog4ivrdlvinst

as

b

,delivery_info

as

c

where

a.saleorder

in

(select

b.ext1_skill

from

ivrlog4ivrdlvinst

where

b.start_date=@date1

and

b.se_id='55'

and

b.ext1_skill!='')

and

convert(varchar(10),a.instime,112)=@date2

order

by

b.start_date

desc,

b.start_time

desc

联合查询效率较高.以下例子来说明联合查询的好处

t1表结构(用户名,密码)userid int username varchar(20) password varchar(20)

1jack jackpwd

2owenowenpwd

t3表结构(用户积分,等级)userid int jf int dj int

1 20 3

3 50 6

第一:内联(inner join)

如果想把用户信息,积分,等级都列出来.那么一般会这样写

select * from t1 ,t3 where t1.userid = t3.userid 其实这样的结果等同于select * from t1 inner join t3 on t1.userid=t3.userid

就是把两个表中都存在userid的行拼成一行.这是内联.但后者的效率会比前者高很多.建议用后者的写法.

运行结果:useridusername password userid jfdj

1 jackjacjpwd 1 20 3

第二:左联（left outer join）显示左表中的所有行

select * from t1 left outer join t3 on t1.userid=t3.userid

运行结果：useridusername password userid jf dj

1jack jackpwd 1 20 3

2 owen owenpwd NULL NULL NULL

第三:右联（right outer join）显示右表中的所有行

select * from t1 right outer join t3 on t1.userid=t3.userid

运行结果：useridusername password userid jf dj

1jack jackpwd 1 20 3

NullNull Null 350 6

第四:全联（full outer join）显示两边表中所有行

select * from t1 full outer join t3 on t1.userid=t3.userid

运行结果：useridusername password userid jf dj

1jack jackpwd 1 20 3

2 owen owenpwd NULL NULL NULL

NullNull Null 350 6

---