有如下几个表:
HR_tblLocation
LocationID int primary key not null
LocationName char 20 not null U_LocationName
LocationManager int not null FK_tblLocation_tblEmployeeHR_tblBranch
BranchID int 10 primary key not null
BranchName char 20 not null
LocationID char 10 not null foreign key (tblLocation.LocationID)
BranchManager int not null FK_tblBranch_tblEmployeeHR_tblDept
DeptID int primary key not null
DeptName char 20 not null
BranchID int not null FK_tblDept_tblBranch
Supervisor int not null FK_tblDept_tblEmployeeHR_tblEmployee
EmpID int primary key not null
EmpName char 30 not null
Classfication char 20 not null
DeptID int not null FK_tblEmployee_tblDept一个Location下可有多个Branch,一个Branch下可有多个Dept,表tblEmployee中的Classfication 中指出该员工是哪一种工作类型(daily, regular, contact, tempory)
现在需根据员工的Classfication统计如下的视图:LocationName BranchName DeptID DeptName Supervisor TotalNoOfEmp NoOfDailyEmp NoOfRegularEmp NoOfContactEmp NoOfTemporyEmp
xxx xxx xxx xxx xxx xxx xxx xxx xxx xxx
xxx xxx xxx xxx xxx xxx xxx xxx xxx xxx
xxx xxx xxx xxx xxx xxx xxx xxx xxx xxx
xxx xxx xxx xxx xxx xxx xxx xxx xxx xxx
xxx xxx xxx xxx xxx xxx xxx xxx xxx xxx我建立了如下视图
alter view HR_vAllXMartInfo as
select L.LocationID, L.LocationName, L.LocationManager, B.BranchID, B.BranchName, B.BranchManager, D.DeptID, D.DeptName, D.Supervisor
from HR_tblLocation as L
left outer join HR_tblBranch as B
on L.LocationID = B.LocationID
left outer join HR_tblDept as D
on D.BranchID = B.BranchID
select * from HR_vAllXMartInfoalter view HR_vAllDeptEmp as
select X.LocationName, X.BranchName, X.DeptID, X.Deptname, X.Supervisor, E.[Role] as Classification
from HR_vAllXMartInfo as X
left outer join HR_tblEmployee as E
on X.DeptID = E.Department_Id
select * from HR_vAllDeptEmp然后使用
select DeptID, count(*) as [Num Of Total Emp]
from HR_vAllDeptEMp
group by DeptID
但是却不正确,不知道是什么问题,还希望高手指导一下.非常感谢!
HR_tblLocation
LocationID int primary key not null
LocationName char 20 not null U_LocationName
LocationManager int not null FK_tblLocation_tblEmployeeHR_tblBranch
BranchID int 10 primary key not null
BranchName char 20 not null
LocationID char 10 not null foreign key (tblLocation.LocationID)
BranchManager int not null FK_tblBranch_tblEmployeeHR_tblDept
DeptID int primary key not null
DeptName char 20 not null
BranchID int not null FK_tblDept_tblBranch
Supervisor int not null FK_tblDept_tblEmployeeHR_tblEmployee
EmpID int primary key not null
EmpName char 30 not null
Classfication char 20 not null
DeptID int not null FK_tblEmployee_tblDept一个Location下可有多个Branch,一个Branch下可有多个Dept,表tblEmployee中的Classfication 中指出该员工是哪一种工作类型(daily, regular, contact, tempory)
现在需根据员工的Classfication统计如下的视图:LocationName BranchName DeptID DeptName Supervisor TotalNoOfEmp NoOfDailyEmp NoOfRegularEmp NoOfContactEmp NoOfTemporyEmp
xxx xxx xxx xxx xxx xxx xxx xxx xxx xxx
xxx xxx xxx xxx xxx xxx xxx xxx xxx xxx
xxx xxx xxx xxx xxx xxx xxx xxx xxx xxx
xxx xxx xxx xxx xxx xxx xxx xxx xxx xxx
xxx xxx xxx xxx xxx xxx xxx xxx xxx xxx我建立了如下视图
alter view HR_vAllXMartInfo as
select L.LocationID, L.LocationName, L.LocationManager, B.BranchID, B.BranchName, B.BranchManager, D.DeptID, D.DeptName, D.Supervisor
from HR_tblLocation as L
left outer join HR_tblBranch as B
on L.LocationID = B.LocationID
left outer join HR_tblDept as D
on D.BranchID = B.BranchID
select * from HR_vAllXMartInfoalter view HR_vAllDeptEmp as
select X.LocationName, X.BranchName, X.DeptID, X.Deptname, X.Supervisor, E.[Role] as Classification
from HR_vAllXMartInfo as X
left outer join HR_tblEmployee as E
on X.DeptID = E.Department_Id
select * from HR_vAllDeptEmp然后使用
select DeptID, count(*) as [Num Of Total Emp]
from HR_vAllDeptEMp
group by DeptID
但是却不正确,不知道是什么问题,还希望高手指导一下.非常感谢!
不过创建应该是CREATE VIEW .. 吧起他好象没什么问题
谢谢zhouhan0048和SQLnewlearner的关注!
on X.DeptID = E.Department_Id
E 没有这个字段Department_Id 阿
是这个原因吗