现在设有简单的表(里面的主键外键就不写了):
Create table Employee( EmpID int, EmpName nvarchar(10), DepID int);
Create table Department( DepID int, DepName nvarchar(10));
Create talbe Manager( EmpID int, DepID int);
如果我要查询部门的信息,包括(部门ID,部门名称,部门经理,部门人数)。这需要多表连查和用到聚集函数。
SQL语句如下:
select D.DepID, DepName,E.EmpName ,G.Num
from t_Department as D
left join t_Manager as M
on M.DepID=D.DepID
left join t_Employee as E
on E.EmpID=M.ManagerID
left join
(select DepID,COUNT(*) as Num
from t_Employee
group by(DepID)
) as G
on D.DepID=G.DepID;
部门表<-->经理表<-->职工表
↑
↓
人数结果
四个结果连接便得到结果