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;
解决方案 »
- PL/SQL执行速度过慢
- 奇怪的问题
- 求一个触发器,本人不懂oracle
- 如何计算执行计划中的cost值
- 建触发器引发的update失败问题!
- 调用存储过程报错:PLS-00306和ORA-06550
- 疑惑:关于Oracle占用内存的问题,能不能在不使用的时候将其内存释放掉?
- 请问各位dx:提示“maximum of 20 enabled roles exceeded“,该如何解决?谢谢。(无内容)
- oracle to sql server
- oracle中怎么设置sessoin中执行一条命令时最多影响到的记录行数
- WIN98上的ORACLE 客户端不能访问WIN2K上的ORACLE数据库服务器?
- 如何使数据文件联机???
像你这种写法,()中子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)