select e.EmployeeNO,e.EmployeeName,d.DEPTNO,d.DEPTNAME from Employee e,Dept d,Business b,BusinessMember m
where e.EmployeeNo in (select m.EmployeeNO from m) and m.BusinessNO in (select BusinessNO from b where b.DEPTNO=d.DEPTNO) group by e.EmployeeNO,d.DEPTNO order by e.EmployeeNO,d.DEPTNO;
where e.EmployeeNo in (select m.EmployeeNO from m) and m.BusinessNO in (select BusinessNO from b where b.DEPTNO=d.DEPTNO) group by e.EmployeeNO,d.DEPTNO order by e.EmployeeNO,d.DEPTNO;
像你这种写法,()中子select 中的表别名m不能识别,会提示
where e.EmployeeNo in (select m.EmployeeNO from m)
*
表或视图不存在
select e.EmployeeNO,e.EmployeeName,d.DEPTNO,d.DEPTNAME from Employee e,Dept d
where e.EmployeeNo in (select m.EmployeeNO from BusinessMember m) and m.BusinessNO in (select b.BusinessNO from Business b where b.DEPTNO=d.DEPTNO) group by e.EmployeeNO,d.DEPTNO order by e.EmployeeNO,d.DEPTNO;
Dept a,Business b,BusinessMember c,Employee d
where a.DeptNO = b.DeptNO
and b.BusinessNO = c.BusinessNO
and c.EmployeeNO(+) = d.EmployeeNO
select employeeno,(select employeename from employee where employeeno=a.employeeno) employeename ,(select deptno from dept where deptno=(select deptno from business where businessno=b.businessno from Business b)) deptno,(select deptname from dept where deptno=(select deptno from business where businessno=c.businessno from Business c)) deptname
from businessmember a)
group by deptno,deptname;对于以查询,若不准确查询数据,那对于楼主表结构表示质疑.另外以下是重新设计表结构:
Employee(EmployeeNO,EmployeeName)
Dept(DeptNO,DeptName)
connect(deptno,employeeno,BusinessNO)--对于三者是多对多关系(即一个员工所对应多个部门多个职务,一个部门对应多个员工)
Business(BusinessNO,BusinessName)