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