有三张表 userinfo(员工信息表), leaveinfo(假期信息表), deptinfo(部门信息表)userinfo :workid ,name,...leaveinfo: workid ,leavedate,...deptinfo: workid, deptname
现在要将三表连接,可能有些人没有假期信息,部门信息,有些人有假期信息,部门信息。现将三表连接,以userinfo为基础表,即使这个人没有假期信息,和部门信息 也要显示出来,怎么做?
现在要将三表连接,可能有些人没有假期信息,部门信息,有些人有假期信息,部门信息。现将三表连接,以userinfo为基础表,即使这个人没有假期信息,和部门信息 也要显示出来,怎么做?
from userinfo inner join deptinfo on userinfo.workid=deptinfo.workid
left join leaveinfo on userinfo.workid=leaveinfo.workid
on A.workid=B.workid full join deptinfo C
on A.workid=C.workid
from userinfo A
Left Join leaveinfo B on B.workid =A.workid
Left Join deptinfo C on C.workid =C.workid
请参考!
from userinfo A
Left Join leaveinfo B on B.workid =A.workid
Left Join deptinfo C on C.workid =C.workid
full join?
把userinfo当成主表即:
select *
from
userinfo A
Left Join leaveinfo B on B.workid =A.workid
--以A表为主表,左连接 leaveinfo
Left Join deptinfo C on A.workid =C.workid
--以A表为主表,左连接 deptinfo
/*左连接把userinfo当主表的意思是,连接之后,
user_info的所有记录全部留下,不管连接后有无匹配。
而leaveinfo,deptinfo如果没有和userinfo匹配的记录,
则会丢掉。
*/
(
workid int primary key,
name varchar(50)
)
insert into userinfo (workid,name) values(1,'zhangsan')
insert into userinfo (workid,name) values(2,'lisi')
insert into userinfo (workid,name) values(3,'wangwu')
create table leaveinfo
(
workid int primary key,
leavedate varchar(50)
)
insert into leaveinfo (workid,leavedate) values(1,5) create table deptinfo
(
workid int primary key,
deptname varchar(50)
)
insert into deptinfo (workid,deptname) values(2,'销售') select *
from userinfo A
Left Join leaveinfo B on B.workid =A.workid
Left Join deptinfo C on A.workid =C.workid
看看符合你的要求不
select
需要的列的列表
from userinfo u
left outer join leaveinfo l
on u.workid = l.workid
left outer join deptinfo d
on u.workid = d.workid