有一个个人信息表,其中有些内容可能为空(指的是代码列,如血型,籍贯等),这样关联代码表时,如果有一项为空,就查不出该列,如果都用左连接,查询结果将有非常多的列。我用下面的方法可以成功执行,但不知道有没有好方法优化。
又因为表名不能用变量,如果想变换查询的表只能用if else了,下面的代码几乎会多一倍。求解,谢谢CREATE PROCEDURE U_student_XH
@xh char(12)
asDeclare @gbdm char(3);
Declare @gbmc varchar(60);
Declare @mzdm char(2);
Declare @mzmc varchar(60);
Declare @zzmmdm char(2);
Declare @zzmmmc varchar(60);
Declare @hyzkdm char(1);
Declare @hyzkmc varchar(40);
Declare @jkzkdm char(2);
Declare @jkzkmc varchar(40);
Declare @xxdm char(1);
Declare @xxmc varchar(40);
Declare @jgdm char(6);
Declare @jgmc varchar(60);
Declare @csddm char(6);
Declare @csdmc varchar(60);
Declare @syddm char(6);
Declare @sydmc varchar(60);
Declare @wyyzdm char(2);
Declare @wyyzmc varchar(40);
Declare @wyspdm char(3);
Declare @wyspmc varchar(40);
Declare @xslydm char(3);
Declare @xslymc varchar(40);
Declare @rxfsdm char(2);
Declare @rxfsmc varchar(40);
Declare @pyfsdm char(2);
Declare @pyfsmc varchar(40);
Declare @xxxsdm char(2);
Declare @xxxsmc varchar(40);
Declare @jdfsdm char(1);
Declare @jdfsmc varchar(40);
Declare @zcdm char(3);
Declare @zcmc varchar(40);
Declare @zwdm char(4);
Declare @zwmc varchar(40);
Declare @hkxzdm char(1);
Declare @hkxzmc varchar(40);
Declare @gatqdm char(1);
Declare @gatqmc varchar(40);
Declare @rxqzhxldm char(2);
Declare @rxqzhxlmc varchar(40);
Declare @rxqzhxwdm char(3);
Declare @rxqzhxwmc varchar(40);
Declare @dxbyxxdm char(5);
Declare @dxbyxxmc varchar(60);
Declare @dxbyzydm char(6);
Declare @dxbyzymc varchar(60);
Declare @ssbyxxdm char(5);
Declare @ssbyxxmc varchar(60);
Declare @ssbyzydm char(6);
Declare @ssbyzymc varchar(60);
Declare @bsbyxxdm char(5);
Declare @bsbyxxmc varchar(60);
Declare @bsbyzydm char(6);
Declare @bsbyzymc varchar(60);
Declare @byshjgdm char(2);
Declare @byshjgmc varchar(40);
Declare @xldm char(2);
Declare @xlmc varchar(40);
Declare @xwdm char(3);
Declare @xwmc varchar(40);SELECT @gbdm=gbdm,@mzdm=mzdm,@zzmmdm=zzmmdm,@hyzkdm=hyzkdm,@jkzkdm=jkzkdm,@xxdm=xxdm,@jgdm=jgdm,@csddm=csddm,@syddm=syddm,@wyyzdm=wyyzdm,@wyspdm=wyspdm,@xslydm=xslydm,@rxfsdm=rxfsdm,@pyfsdm=pyfsdm,@xxxsdm=xxxsdm,@jdfsdm=jdfsdm,@zcdm=zcdm,@zwdm=zwdm,@hkxzdm=hkxzdm,@gatqdm=gatqdm,@rxqzhxldm=rxqzhxldm,@rxqzhxwdm=rxqzhxwdm,@dxbyxxdm=dxbyxxdm,@dxbyzydm=dxbyzydm,@ssbyxxdm=ssbyxxdm,@ssbyzydm=ssbyzydm,@bsbyxxdm=bsbyxxdm,@bsbyzydm=bsbyzydm,@byshjgdm=byshjgdm,@xldm=xldm,@xwdm=xwdm
from Dxsxxxx
where xh = @xhselect @gbmc=gbmc from cgb where gbdm=@gbdm
select @mzmc=mzmc from cmz where mzdm=@mzdm
select @zzmmmc=zzmmmc from czzmm where zzmmdm=@zzmmdm
select @hyzkmc=hyzkmc from chyzk where hyzkdm=@hyzkdm
select @jkzkmc=jkzkmc from cjkzk where jkzkdm=@jkzkdm
select @xxmc=xxmc from cxx where xxdm=@xxdm
select @jgmc=xzqhmc from cxzqh where xzqhdm=@jgdm
select @csdmc=xzqhmc from cxzqh where xzqhdm=@csddm
select @sydmc=xzqhmc from cxzqh where xzqhdm=@syddm
select @wyyzmc=wyyzmc from cwyyz where wyyzdm=@wyyzdm
select @wyspmc=wyspmc from cwysp where wyspdm=@wyspdm
select @xslymc=xslymc from cxsly where xslydm=@xslydm
select @rxfsmc=rxfsmc from crxfs where rxfsdm=@rxfsdm
select @pyfsmc=pyfsmc from cpyfs where pyfsdm=@pyfsdm
select @xxxsmc=xxxsmc from cxxxs where xxxsdm=@xxxsdm
select @jdfsmc=jdfsmc from cjdfs where jdfsdm=@jdfsdm
select @zcmc=zcmc from czc where zcdm=@zcdm
select @zwmc=zwmc from czw where zwdm=@zwdm
select @hkxzmc=hkxzmc from chkxz where hkxzdm=@hkxzdm
select @gatqmc=gatqmc from cgatq where gatqdm=@gatqdm
select @rxqzhxlmc=xlmc from cxl where xldm=@rxqzhxldm
select @rxqzhxwmc=xwmc from cxw where xwdm=@rxqzhxwdm
select @dxbyxxmc=gdxxmc from cgdxx where gdxxdm=@dxbyxxdm
select @dxbyzymc=bzkzymc from cbzkzy where bzkzydm=@dxbyzydm
select @ssbyxxmc=gdxxmc from cgdxx where gdxxdm=@ssbyxxdm
select @ssbyzymc=bsszymc from cbsszy where bsszydm=@ssbyzydm
select @bsbyxxmc=gdxxmc from cgdxx where gdxxdm=@bsbyxxdm
select @bsbyzymc=bsszymc from cbsszy where bsszydm=@bsbyzydm
select @byshjgmc=byshjgmc from cbyshjg where byshjgdm=@byshjgdm
select @xlmc=xlmc from cxl where xldm=@xldm
select @xwmc=xwmc from cxw where xwdm=@xwdm
select *,@gbmc as gbmc, @mzmc as mzmc, @zzmmmc as zzmmmc, @hyzkmc as hyzkmc, @jkzkmc as jkzkmc, @xxmc as xxmc, @jgmc as jgmc, @csdmc as csdmc, @sydmc as sydmc, @wyyzmc as wyyzmc, @wyspmc as wyspmc, @xslymc as xslymc, @rxfsmc as rxfsmc, @pyfsmc as pyfsmc, @xxxsmc as xxxsmc, @jdfsmc as jdfsmc, @zcmc as zcmc, @zwmc as zwmc, @hkxzmc as hkxzmc, @gatqmc as gatqmc, @rxqzhxlmc as rxqzhxlmc, @rxqzhxwmc as rxqzhxwmc, @dxbyxxmc as dxbyxxmc, @dxbyzymc as dxbyzymc, @ssbyxxmc as ssbyxxmc, @ssbyzymc as ssbyzymc, @bsbyxxmc as bsbyxxmc, @bsbyzymc as bsbyzymc, @byshjgmc as byshjgmc, @xlmc as xlmc, @xwmc as xwmc
from Dxsxxxx
where xh = @xhGO
又因为表名不能用变量,如果想变换查询的表只能用if else了,下面的代码几乎会多一倍。求解,谢谢CREATE PROCEDURE U_student_XH
@xh char(12)
asDeclare @gbdm char(3);
Declare @gbmc varchar(60);
Declare @mzdm char(2);
Declare @mzmc varchar(60);
Declare @zzmmdm char(2);
Declare @zzmmmc varchar(60);
Declare @hyzkdm char(1);
Declare @hyzkmc varchar(40);
Declare @jkzkdm char(2);
Declare @jkzkmc varchar(40);
Declare @xxdm char(1);
Declare @xxmc varchar(40);
Declare @jgdm char(6);
Declare @jgmc varchar(60);
Declare @csddm char(6);
Declare @csdmc varchar(60);
Declare @syddm char(6);
Declare @sydmc varchar(60);
Declare @wyyzdm char(2);
Declare @wyyzmc varchar(40);
Declare @wyspdm char(3);
Declare @wyspmc varchar(40);
Declare @xslydm char(3);
Declare @xslymc varchar(40);
Declare @rxfsdm char(2);
Declare @rxfsmc varchar(40);
Declare @pyfsdm char(2);
Declare @pyfsmc varchar(40);
Declare @xxxsdm char(2);
Declare @xxxsmc varchar(40);
Declare @jdfsdm char(1);
Declare @jdfsmc varchar(40);
Declare @zcdm char(3);
Declare @zcmc varchar(40);
Declare @zwdm char(4);
Declare @zwmc varchar(40);
Declare @hkxzdm char(1);
Declare @hkxzmc varchar(40);
Declare @gatqdm char(1);
Declare @gatqmc varchar(40);
Declare @rxqzhxldm char(2);
Declare @rxqzhxlmc varchar(40);
Declare @rxqzhxwdm char(3);
Declare @rxqzhxwmc varchar(40);
Declare @dxbyxxdm char(5);
Declare @dxbyxxmc varchar(60);
Declare @dxbyzydm char(6);
Declare @dxbyzymc varchar(60);
Declare @ssbyxxdm char(5);
Declare @ssbyxxmc varchar(60);
Declare @ssbyzydm char(6);
Declare @ssbyzymc varchar(60);
Declare @bsbyxxdm char(5);
Declare @bsbyxxmc varchar(60);
Declare @bsbyzydm char(6);
Declare @bsbyzymc varchar(60);
Declare @byshjgdm char(2);
Declare @byshjgmc varchar(40);
Declare @xldm char(2);
Declare @xlmc varchar(40);
Declare @xwdm char(3);
Declare @xwmc varchar(40);SELECT @gbdm=gbdm,@mzdm=mzdm,@zzmmdm=zzmmdm,@hyzkdm=hyzkdm,@jkzkdm=jkzkdm,@xxdm=xxdm,@jgdm=jgdm,@csddm=csddm,@syddm=syddm,@wyyzdm=wyyzdm,@wyspdm=wyspdm,@xslydm=xslydm,@rxfsdm=rxfsdm,@pyfsdm=pyfsdm,@xxxsdm=xxxsdm,@jdfsdm=jdfsdm,@zcdm=zcdm,@zwdm=zwdm,@hkxzdm=hkxzdm,@gatqdm=gatqdm,@rxqzhxldm=rxqzhxldm,@rxqzhxwdm=rxqzhxwdm,@dxbyxxdm=dxbyxxdm,@dxbyzydm=dxbyzydm,@ssbyxxdm=ssbyxxdm,@ssbyzydm=ssbyzydm,@bsbyxxdm=bsbyxxdm,@bsbyzydm=bsbyzydm,@byshjgdm=byshjgdm,@xldm=xldm,@xwdm=xwdm
from Dxsxxxx
where xh = @xhselect @gbmc=gbmc from cgb where gbdm=@gbdm
select @mzmc=mzmc from cmz where mzdm=@mzdm
select @zzmmmc=zzmmmc from czzmm where zzmmdm=@zzmmdm
select @hyzkmc=hyzkmc from chyzk where hyzkdm=@hyzkdm
select @jkzkmc=jkzkmc from cjkzk where jkzkdm=@jkzkdm
select @xxmc=xxmc from cxx where xxdm=@xxdm
select @jgmc=xzqhmc from cxzqh where xzqhdm=@jgdm
select @csdmc=xzqhmc from cxzqh where xzqhdm=@csddm
select @sydmc=xzqhmc from cxzqh where xzqhdm=@syddm
select @wyyzmc=wyyzmc from cwyyz where wyyzdm=@wyyzdm
select @wyspmc=wyspmc from cwysp where wyspdm=@wyspdm
select @xslymc=xslymc from cxsly where xslydm=@xslydm
select @rxfsmc=rxfsmc from crxfs where rxfsdm=@rxfsdm
select @pyfsmc=pyfsmc from cpyfs where pyfsdm=@pyfsdm
select @xxxsmc=xxxsmc from cxxxs where xxxsdm=@xxxsdm
select @jdfsmc=jdfsmc from cjdfs where jdfsdm=@jdfsdm
select @zcmc=zcmc from czc where zcdm=@zcdm
select @zwmc=zwmc from czw where zwdm=@zwdm
select @hkxzmc=hkxzmc from chkxz where hkxzdm=@hkxzdm
select @gatqmc=gatqmc from cgatq where gatqdm=@gatqdm
select @rxqzhxlmc=xlmc from cxl where xldm=@rxqzhxldm
select @rxqzhxwmc=xwmc from cxw where xwdm=@rxqzhxwdm
select @dxbyxxmc=gdxxmc from cgdxx where gdxxdm=@dxbyxxdm
select @dxbyzymc=bzkzymc from cbzkzy where bzkzydm=@dxbyzydm
select @ssbyxxmc=gdxxmc from cgdxx where gdxxdm=@ssbyxxdm
select @ssbyzymc=bsszymc from cbsszy where bsszydm=@ssbyzydm
select @bsbyxxmc=gdxxmc from cgdxx where gdxxdm=@bsbyxxdm
select @bsbyzymc=bsszymc from cbsszy where bsszydm=@bsbyzydm
select @byshjgmc=byshjgmc from cbyshjg where byshjgdm=@byshjgdm
select @xlmc=xlmc from cxl where xldm=@xldm
select @xwmc=xwmc from cxw where xwdm=@xwdm
select *,@gbmc as gbmc, @mzmc as mzmc, @zzmmmc as zzmmmc, @hyzkmc as hyzkmc, @jkzkmc as jkzkmc, @xxmc as xxmc, @jgmc as jgmc, @csdmc as csdmc, @sydmc as sydmc, @wyyzmc as wyyzmc, @wyspmc as wyspmc, @xslymc as xslymc, @rxfsmc as rxfsmc, @pyfsmc as pyfsmc, @xxxsmc as xxxsmc, @jdfsmc as jdfsmc, @zcmc as zcmc, @zwmc as zwmc, @hkxzmc as hkxzmc, @gatqmc as gatqmc, @rxqzhxlmc as rxqzhxlmc, @rxqzhxwmc as rxqzhxwmc, @dxbyxxmc as dxbyxxmc, @dxbyzymc as dxbyzymc, @ssbyxxmc as ssbyxxmc, @ssbyzymc as ssbyzymc, @bsbyxxmc as bsbyxxmc, @bsbyzymc as bsbyzymc, @byshjgmc as byshjgmc, @xlmc as xlmc, @xwmc as xwmc
from Dxsxxxx
where xh = @xhGO
把表結構貼出來看看,這麼寫有點..%&%&
declare @gbdm char(3)
,@gbmc varchar(60)
,@mzdm char(2)
,@mzmc varchar(60)
,@zzmmdm char(2)
,@zzmmmc varchar(60)
,@hyzkdm char(1)
,@hyzkmc varchar(40)
,@jkzkdm char(2)
,@jkzkmc varchar(40)
,@xxdm char(1)
,@xxmc varchar(40)
,@jgdm char(6)
,@jgmc varchar(60)
,@csddm char(6)
,@csdmc varchar(60)
,@syddm char(6)
,@sydmc varchar(60)
,@wyyzdm char(2)
,@wyyzmc varchar(40)
,@wyspdm char(3)
,@wyspmc varchar(40)
,@xslydm char(3)
,@xslymc varchar(40)
,@rxfsdm char(2)
,@rxfsmc varchar(40)
,@pyfsdm char(2)
,@pyfsmc varchar(40)
,@xxxsdm char(2)
,@xxxsmc varchar(40)
,@jdfsdm char(1)
,@jdfsmc varchar(40)
,@zcdm char(3)
,@zcmc varchar(40)
,@zwdm char(4)
,@zwmc varchar(40)
,@hkxzdm char(1)
,@hkxzmc varchar(40)
,@gatqdm char(1)
,@gatqmc varchar(40)
,@rxqzhxldm char(2)
,@rxqzhxlmc varchar(40)
,@rxqzhxwdm char(3)
,@rxqzhxwmc varchar(40)
,@dxbyxxdm char(5)
,@dxbyxxmc varchar(60)
,@dxbyzydm char(6)
,@dxbyzymc varchar(60)
,@ssbyxxdm char(5)
,@ssbyxxmc varchar(60)
,@ssbyzydm char(6)
,@ssbyzymc varchar(60)
,@bsbyxxdm char(5)
,@bsbyxxmc varchar(60)
,@bsbyzydm char(6)
,@bsbyzymc varchar(60)
,@byshjgdm char(2)
,@byshjgmc varchar(40)
,@xldm char(2)
,@xlmc varchar(40)
,@xwdm char(3)
,@xwmc varchar(40)
--------------
如果要看表结构~~那一定比现在还要恐怖....看他有多少个表......
1:男
2:女
3:未知性别
9:其他在这基础上,增加一项
-1 空字符串那么查询就可以应用inner join方式查询了,效率比left join就会有显著提高
其他代码表同样的道理其实很多的表设计理论中已经探讨过该问题,如何避免left join了