SELECT e1.*,d.dname
FROM emp e1,emp e2,dept d WHERE e2.empno=e1.mgr(+)
AND e2.deptno=d.deptno;
FROM emp e1,emp e2,dept d WHERE e2.empno=e1.mgr(+)
AND e2.deptno=d.deptno;
解决方案 »
- 在Redhat AS4上基于ASM存储的ORACLE 10g单实例数据库安装
- 怎麼建立關聯兩個scheam中table的儲存過程?
- 一个sql语句
- 求助关于SYS账号的一个很诡异的问题,高手一定要来看!
- 方案的导出与导入?
- 执行一条sql语句后,如何看到它的执行效率?
- oracle8i的程序,将数据库导入oracle9.2后,程序不能正常运行,程序是ODBC的程序。数据库在本机。
- 直接将Oralce81\Database中的内容全部考下来,能实现对以前数据库的备份吗?
- 急!是关于数据恢复的问题
- from 后做嵌套后老是提示表或试图不存在,请高手指点错误
- oracle sql参数值问题
- MySql关联查询时候少数据,求大神帮忙看看
FROM emp e1,emp e2,dept d
WHERE e1.mgr=e2.empno(+)
AND e2.deptno=d.deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DNAME
------- ---------- --------- ---------- -------------- ---------- ---------- ---------- ---------
7369 SMITH CLERK 7902 17-12月-80 800 20 RESEARCH
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 SALES
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 SALES
7566 JONES MANAGER 7839 02-4月 -81 2975 20 ACCOUNTING
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 SALES
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 ACCOUNTING
7782 CLARK MANAGER 7839 09-6月 -81 2450 10 ACCOUNTING
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 RESEARCH
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 SALES
7876 ADAMS CLERK 7788 23-5月 -87 1100 20 RESEARCH
7900 JAMES CLERK 7698 03-12月-81 950 30 SALES
7902 FORD ANALYST 7566 03-12月-81 3000 20 RESEARCH
7934 MILLER CLERK 7782 23-1月 -82 1300 10 ACCOUNTING
SALES
SALES
SALES
SALES
ACCOUNTING
RESEARCH
RESEARCH
SALES结果不对劲啊
------- ---------- --------- ---------- -------------- ---------- ---------- ---------- ---------
7369 SMITH CLERK 7902 17-12月-80 800 20 RESEARCH
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 SALES
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 SALES
7566 JONES MANAGER 7839 02-4月 -81 2975 20 ACCOUNTING
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 SALES
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 ACCOUNTING
7782 CLARK MANAGER 7839 09-6月 -81 2450 10 ACCOUNTING
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 RESEARCH
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 SALES
7876 ADAMS CLERK 7788 23-5月 -87 1100 20 RESEARCH
7900 JAMES CLERK 7698 03-12月-81 950 30 SALES
7902 FORD ANALYST 7566 03-12月-81 3000 20 RESEARCH
7934 MILLER CLERK 7782 23-1月 -82 1300 10 ACCOUNTING结果还是一样啊
SELECT e1.*,d.dname
FROM emp e1,emp e2,dept d
WHERE e1.mgr=e2.empno(+)
AND e2.deptno=d.deptno(+);
FROM EMP E1, EMP E2, DEPT D
WHERE E1.MGR=E2.EMPNO(+)
AND E2.DEPTNO = D.DEPTNO;
SELECT e1.*,d.dname
FROM emp e1,emp e2,dept d
WHERE e1.mgr=e2.empno(+)
AND e2.deptno=d.deptno(+);
谢谢貌似成功了:
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DNAME
------ ---------- --------- ---------- -------------- ---------- ---------- ---------- ---------
7566 JONES MANAGER 7839 02-4月 -81 2975 20 ACCOUNTING
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 ACCOUNTING
7782 CLARK MANAGER 7839 09-6月 -81 2450 10 ACCOUNTING
7934 MILLER CLERK 7782 23-1月 -82 1300 10 ACCOUNTING
7369 SMITH CLERK 7902 17-12月-80 800 20 RESEARCH
7876 ADAMS CLERK 7788 23-5月 -87 1100 20 RESEARCH
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 RESEARCH
7902 FORD ANALYST 7566 03-12月-81 3000 20 RESEARCH
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 SALES
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 SALES
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 SALES
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 SALES
7900 JAMES CLERK 7698 03-12月-81 950 30 SALES
7839 KING PRESIDENT 17-11月-81 5000 10
KING是有部门的
---------- -------------- -------------
10 ACCOUNTING NEW YORK
---------- -------------- -------------
10 ACCOUNTING NEW YORK
我看漏了说错了,会不会是King对应那行的10左右有空格什么的
---------- -------------- -------------
10 ACCOUNTING NEW YORK
我看漏了说错了,会不会是King对应那行的10左右有空格什么的
也不应该是这个问题啊:
select *from emp where deptno=10; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------ ---------- --------- ---------- -------------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7839 KING PRESIDENT 17-11月-81 5000 10
7934 MILLER CLERK 7782 23-1月 -82 1300 10
这样明明都查的出来
SELECT e1.*,d.dname
FROM emp e1,emp e2,dept d
WHERE e1.mgr=e2.empno(+)
AND e2.deptno=d.deptno(+);
能解释一下为什么用两个+号吗?
FROM emp e1,dept d
WHERE e1.deptno=d.deptno;
这样king能不能出来
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DNAME
------ ---------- --------- ---------- -------------- ---------- ---------- ---------- ---------
7782 CLARK MANAGER 7839 09-6月 -81 2450 10 ACCOUNTING
7839 KING PRESIDENT 17-11月-81 5000 10 ACCOUNTING
7934 MILLER CLERK 7782 23-1月 -82 1300 10 ACCOUNTING
7566 JONES MANAGER 7839 02-4月 -81 2975 20 RESEARCH
7902 FORD ANALYST 7566 03-12月-81 3000 20 RESEARCH
7876 ADAMS CLERK 7788 23-5月 -87 1100 20 RESEARCH
7369 SMITH CLERK 7902 17-12月-80 800 20 RESEARCH
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 RESEARCH
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 SALES
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 SALES
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 SALES
7900 JAMES CLERK 7698 03-12月-81 950 30 SALES
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 SALES
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 SALES