select a.empid,b.empname,d.posiname,c.deptname from empdept a join employee b on a.empid=b.empid
join dept c on a.deptid=c.deptid join posi d on a.posiid=d.posiid
join dept c on a.deptid=c.deptid join posi d on a.posiid=d.posiid
a(0,1,1),b(1,张三),c(0,经营部),d(1,部长)...
from employee A inner join empdept B on B.empid=A.empid
inner join dept C on deptid=B.deptid
inner join posi D on posiid=B.posiid
(select posiname from posi where posiid=A.posiid),
(select deptname from dept where deptid=A.deptid)
from empdept A
from empdept A,employee B,dept C,posi D
where B.empid=A.empid and C.deptid=A.deptid and D.posiid=A.posiid
或者
select B.empid,A.empname,D.posiname,C.deptname
from empdept B inner join employee A on B.empid=A.empid
inner join dept C on deptid=B.deptid
inner join posi D on posiid=B.posiid
left join dept c on a.deptid=c.deptid left join posi d on a.posiid=d.posiid
,b.empname
,c.posiname
,d.deptname
from empdept a
full join employee b on a.empid=b.empid
full join posi c on a.posiid=c.posiid
full join dept d on a.deptid=d.deptid
insert into @empdept
select 0,1,1declare @employee table(empid int,empname varchar(10))
insert into @employee
select 1,'张三'declare @dept table (deptid int,deptname varchar(10))
insert into @dept
select 0,'经营部'declare @posi table(posiid int,posiname varchar(10))
insert into @posi
select 1,'部长'select a.empid
,b.empname
,c.posiname
,d.deptname
from @empdept a
full join @employee b on a.empid=b.empid
full join @posi c on a.posiid=c.posiid
full join @dept d on a.deptid=d.deptid
/*--结果
empid empname posiname deptname
----------- ---------- ---------- ----------
NULL 张三 NULL NULL
0 NULL 部长 NULL
NULL NULL NULL 经营部(所影响的行数为 3 行)--*/
怎么样查出empid,empname,posiname,deptname.-----------------------
select
empid,empname,posiname,deptname
from empdept a
join employee b on a.empid=b.empid
join dept c on a.deptid=c.deptid
join posi d on a.posiid=d.posiid