有个表 属性有员工的编号ID 员工的名字name;
另外一个表里面有部门的编号tID 部门的名字tname,部门经理的编号MID,总监的名字DId;
现在要新建一个view 里面有部门的编号,部门的名字 部门经理的名字 和总监的名字
现有的sql语句:
select tba.tID,tba.tname,tba.MName,tbb.DName
from
(select b.tID,b.tname,a.name as MName
from a,b
where a.ID = b.MID) as tba,
(select b.tID,b.tname,a.name as DName
from a,b
where a.ID = b.DId) as tbb
where tba.tID = tbb.tID
但发现效率不太高,请问有什么高效的方法吗
另外一个表里面有部门的编号tID 部门的名字tname,部门经理的编号MID,总监的名字DId;
现在要新建一个view 里面有部门的编号,部门的名字 部门经理的名字 和总监的名字
现有的sql语句:
select tba.tID,tba.tname,tba.MName,tbb.DName
from
(select b.tID,b.tname,a.name as MName
from a,b
where a.ID = b.MID) as tba,
(select b.tID,b.tname,a.name as DName
from a,b
where a.ID = b.DId) as tbb
where tba.tID = tbb.tID
但发现效率不太高,请问有什么高效的方法吗
from a a
left join b b1 on a.id=b.mid
left join b b2 on a.id=b.did
where b1.tid=b2.tid
select
tid,tname,mid,did
from
tb
select
b1.tid.b1.tname,b2.mname,b2.dname
from
tb a
left join
tb b1 on a.id=b.mid
left join
tb b2 on a.id=b.did
where
b1.tid=b2.tid
from b
left join a a1 on a1.id=b.mid
left join a a2 on a2.id=b.did