select A.ygbh, A.ygxm, A.bmbh, isnull(B.bmmc, A.bmbh) bmmc, A.lz
from 员工 as A left join 部门 as B on A.bmbh = B.bmbhselect A.ygbh, A.ygxm, A.bmbh, isnull(B.bmmc, A.bmbh) bmmc, A.lz
from 员工 as A left join 部门 as B on A.bmbh = B.bmbh
where A.lz = 1
from 员工 as A left join 部门 as B on A.bmbh = B.bmbhselect A.ygbh, A.ygxm, A.bmbh, isnull(B.bmmc, A.bmbh) bmmc, A.lz
from 员工 as A left join 部门 as B on A.bmbh = B.bmbh
where A.lz = 1
select a.ygbh,a.ygxm,a.bmbh,case b.bmmc when null then a.bmbh else b.bmmc,a.lz from 员工 a left join 部门 b on a.bmbh=b.bmbh
declare @emp table(yghb varchar(10),ygxm varchar(10),bmbh varchar(10),lz varchar(10))insert @dep select 'b1','m1'
insert @dep select 'b2','m2'
insert @emp select 'y1','xm1','b1','0'
insert @emp select 'y2','xm2','b2','1'
insert @emp select 'y3','xm2','b3','1'select * from @dep
select * from @empselect a.yghb,a.ygxm,a.bmbh,isnull(b.bmmc,a.bmbh) bmmc,a.lz from @emp a
left join @dep b
on a.bmbh = b.bmbh
left join @dep b
on a.bmbh = b.bmbh
where a.lz = 1
select a.*,isnull(b.bmmc,b.bmbh) from a left join b on a.bmbh=b.bmbh用这个,老是提示字段bmmc不存在。我如果写成:select a.*,b.* from a left join b on a.bmbh=b.bmbh就可以了,为什么?
case when b.bmbh is null
then a.bmbh
else b.bmmc
end , a.lz
from 员工 as a left join 部门 as b
on a.bmbh=b.bmbh
你的第二个要求
select a.ygbh , a.ygxm, a.bmbh ,
case when b.bmbh is null
then a.bmbh
else b.bmmc
end , a.lz
from 员工 as a left join 部门 as b
on a.bmbh=b.bmbh
where a.lz=1