sql多表连接查询

今天的天气：晴

一、临时表

,

if OBJECT_ID('#temp_a'is not null drop table #temp_a --已存在则删除 

create table #temp_a --创建临时表

 ( XingMing char(15) , XueShengID int )

 insert into #temp_a( XingMing ,XueShengID ) 

select b.XingMing,a.XueWeiID 

from FuXiu_BanJi a,XueJi_XueSheng b 

where a.XueWeiID=b.XueShengID

二、对于多表查询时，一个表向另一个表提取同一列数据的问题，不管是使用where条件还是使用inner join来提取，最好是用table1 A ,table2 B

例子：

代码：

select    rtrim(ltrim(FuXiuBanMing))as 辅修班名

,rtrim(ltrim(FuXiu_ZhuanYe.ZhuanYeMingCheng))as 专业名称

,rtrim(ltrim(JiChu_JiaoXueQu.JiaoXueQuMing))as 教学区

,rtrim(ltrim(JiChu_JiaoShi.XingMing))as 班主任,

rtrim(ltrim(a.XingMing))as 班长

,rtrim(ltrim(b.XingMing))as 学委

,FuXiu_BanJiID

,rtrim(ltrim(FuXiu_BanJi.RenShu))as 人数

,FuXiu_BanJi.ShiFouBiYe 是否毕业

,FuXiu_BanJi.ShiFouPaiKe 是否排课

,FuXiu_BanJi.ShiFouDengJiChanXun 是否登记查询

,rtrim(ltrim(FuXiu_BanJi.BeiZhu))as 备注

,FuXiu_BanJi.FuXiu_ZhuanYeID

,FuXiu_BanJi.JiaoXueQuID

,FuXiu_BanJi.BanZhuRenID

,FuXiu_BanJi.BanZhanID

,FuXiu_BanJi.XueWeiID

,FuXiu_ZhuanYe .NiaJjiID 

from      FuXiu_BanJi

left outer join  FuXiu_ZhuanYe 

on FuXiu_ZhuanYe .FuXiu_ZhuanYeID =FuXiu_BanJi .FuXiu_ZhuanYeID 

left outer join JiChu_JiaoXueQu 

on JiChu_JiaoXueQu .JiaoXueQuIID =FuXiu_BanJi .JiaoXueQuID 

left outer join JiChu_JiaoShi 

on JiChu_JiaoShi .JiaoShiID =FuXiu_BanJi .BanZhuRenID 

left outer join XueJi_XueSheng a

on a.XueShengID = FuXiu_BanJi .BanZhanID 

left outer join XueJi_XueSheng b 

on b .XueShengID =FuXiu_BanJi .XueWeiID

where FuXiu_BanJi .ShiFouZuoFei =0

order by FuXiu_BanJi .FuXiu_BanJiID ASC

 

三、多表查询

〈①where语句丿

select 

d. ZhuanYeMingCheng 

,e.JiaoXueQuMing 

,f.XingMing as 班主任

,b.XingMing as 班长

,c.XingMing as 学委

,a.FuXiu_BanJiID,a.FuXiuBanMing

,a.RenShu  

,a.ShiFouBiYe as 是否毕业

,a.ShiFouPaiKe as 是否排课

,a .ShiFouDengJiChanXun  as 是否登记查询

,a .BeiZhu 

from        FuXiu_BanJi a

,XueJi_XueSheng  b 

,#temp_a c  

,FuXiu_ZhuanYe d

,JiChu_JiaoXueQu e

,JiChu_JiaoShi f

where a.BanZhanID =b.XueShengID 

and c.XueShengID=a.XueWeiID 

and d .FuXiu_ZhuanYeID =a .FuXiu_ZhuanYeID 

and e .JiaoXueQuIID =a .JiaoXueQuID

and

f .JiaoShiID =a .BanZhuRenID 

<②、inner join
select  

FuXiu_ZhuanYe . ZhuanYeMingCheng as 专业名称 

,JiChu_JiaoXueQu . JiaoXueQuMing as 教学区  

,JiChu_JiaoShi .XingMing as 班主任 

,XueJi_XueSheng .XingMing as 班长

, #temp_a.XingMing as 学委

,FuXiu_BanJiID,FuXiuBanMing  

,FuXiu_BanJi .RenShu  

,FuXiu_BanJi .ShiFouBiYe 

,FuXiu_BanJi .ShiFouPaiKe 

,FuXiu_BanJi .ShiFouDengJiChanXun 

,FuXiu_BanJi .BeiZhu 

from  FuXiu_ZhuanYe 

inner join FuXiu_BanJi 

on FuXiu_ZhuanYe .FuXiu_ZhuanYeID =FuXiu_BanJi .FuXiu_ZhuanYeID 

inner join JiChu_JiaoXueQu 

on JiChu_JiaoXueQu .JiaoXueQuIID =FuXiu_BanJi .JiaoXueQuID 

inner join JiChu_JiaoShi 

on JiChu_JiaoShi .JiaoShiID =FuXiu_BanJi .BanZhuRenID 

inner join XueJi_XueSheng

on XueJi_XueSheng .XueShengID = FuXiu_BanJi .BanZhanID  

inner join #temp_a 

on #temp_a .XueShengID =FuXiu_BanJi .XueWeiID 

XueJi_XueSheng a,XueJi_XueSheng b（临时表慎用）

 

 

四、left outer join 多表连接无对应时，返回空值 

 

select                      rtrim(ltrim(FuXiuBanMing))as 辅修班名

,rtrim(ltrim(FuXiu_ZhuanYe.ZhuanYeMingCheng))as 专业名称
,rtrim(ltrim(JiChu_JiaoXueQu.JiaoXueQuMing))as 教学区
,rtrim(ltrim(JiChu_JiaoShi.XingMing))as 班主任
,rtrim(ltrim(a.XingMing))as 班长
,rtrim(ltrim(b.XingMing))as 学委
,FuXiu_BanJiID
,rtrim(ltrim(FuXiu_BanJi.RenShu))as 人数
,FuXiu_BanJi.ShiFouBiYe 是否毕业
,FuXiu_BanJi.ShiFouPaiKe 是否排课
,FuXiu_BanJi.ShiFouDengJiChanXun 是否登记查询
,rtrim(ltrim(FuXiu_BanJi.BeiZhu))as 备注
,FuXiu_BanJi.FuXiu_ZhuanYeID
,FuXiu_BanJi.JiaoXueQuID
,FuXiu_BanJi.BanZhuRenID
,FuXiu_BanJi.BanZhanID
,FuXiu_BanJi.XueWeiID
,FuXiu_ZhuanYe .NiaJjiID 
from FuXiu_BanJi
left outer join  FuXiu_ZhuanYe 
on FuXiu_ZhuanYe .FuXiu_ZhuanYeID =FuXiu_BanJi .FuXiu_ZhuanYeID 
left outer join JiChu_JiaoXueQu 
on JiChu_JiaoXueQu .JiaoXueQuIID =FuXiu_BanJi .JiaoXueQuID 
left outer join JiChu_JiaoShi 
on JiChu_JiaoShi .JiaoShiID =FuXiu_BanJi .BanZhuRenID 
left outer join XueJi_XueSheng a
on a.XueShengID = FuXiu_BanJi .BanZhanID 
left outer join XueJi_XueSheng b 
on b .XueShengID =FuXiu_BanJi .XueWeiID
where FuXiu_BanJi .ShiFouZuoFei =0
order by FuXiu_BanJi .FuXiu_BanJiID ASC