- SQL code
select * from dbo.tbl_staffMove ←全查语句 联查语句↓select sm.id,st.uname,dp.name as oldname,dp1.name as newdname, sm.kind,sm.remark,sm.createTime,ad.loginName from tbl_staffMove as sm inner join tbl_staff as st on st.id = sm.staffId left join tbl_admin as ad on ad.id = sm.adminId left join tbl_departMent as dp on dp.id = sm.OldDepart --从调动表中获取原部门 left join tbl_departMent as dp1 on dp1.id = st.departId --从人员表中获取新部门
查询结果 全查结果
id staffId oldDepart newDepart kind remark adminId createTime
--------------------------------------------------------
35 47 11 0 0 NULL 2011-12-23 00:00:00.000
36 48 11 0 0 2 2011-12-15 00:00:00.000
37 50 18 0 0 ss NULL 2011-12-01 00:00:00.000
联查结果
id uname oldname newdname kind remark createTime loginName
-------------------------------------- ---------------------
35 张辉 项目小组xiao 项目组 0 2011-12-23 00:00:00.000 NULL
36 张凯 项目小组xiao NULL 0 2011-12-15 00:00:00.000 aa
37 张三丰 朔明电子 项目小组xiao 0 2011-12-01 00:00:00.000 NULL
各位大哥大姐们... 叔叔阿姨们.. 上面全查出来的 newDepart 和地下的 newdname 为神马就不一样? 地下的应该全部是NULL 啊、
------解决方案--------------------
- SQL code
select sm.id,st.uname,dp.name as oldname,dp1.name as newdname, sm.kind,sm.remark,sm.createTime,ad.loginName from tbl_staffMove as sm inner join tbl_staff as st on st.id = sm.staffId left join tbl_admin as ad on ad.id = sm.adminId left join tbl_departMent as dp on dp.id = sm.OldDepart --从调动表中获取原部门 left join tbl_departMent as dp1 on dp1.id = sm.newDepart --从人员表中获取新部门