比较两表数据

来源：互联网发布：mac live photo 编辑编辑：程序博客网时间：2024/05/16 12:47
CREATE OR REPLACE VIEW v ASSELECT * FROM emp WHERE deptno != 10UNION ALLSELECT * FROM emp WHERE ename = 'WARD';SQL> set linesize 200SQL> select * from v where v.ename='WARD';     EMPNO ENAME      JOBMGR HIREDATE  SAL    COMM     DEPTNO---------- ---------- ---------- ---------- -------------- ---------- ---------- ----------      7521 WARD       SALESMAN       7698 22-2月 -81 1250     500 30      7521 WARD       SALESMAN       7698 22-2月 -81 1250     500 30SQL> select * from emp where ename='WARD';     EMPNO ENAME      JOBMGR HIREDATE  SAL    COMM     DEPTNO---------- ---------- ---------- ---------- -------------- ---------- ---------- ----------      7521 WARD       SALESMAN       7698 22-2月 -81 1250     500 30用全关联的话:SQL> SELECT v.empno, v.ename, b.empno, b.ename  FROM v  FULL JOIN emp b ON (b.empno = v.empno) WHERE (v.empno IS NULL OR b.empno IS NULL);  2    3    4       EMPNO ENAME   EMPNO ENAME---------- ---------- ---------- ----------    7839 KING    7782 CLARK    7934 MILLER会丢失ENAME=WARD的数据,可以使用标量子查询:SQL>  SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno,               /*这儿使用的语句就叫标量子查询，以主表返回每一行的值为条件进行查询*/               (SELECT COUNT(*) FROM v v2 WHERE v2.empno = v.empno) AS cnt          FROM v  2    3    4  ;     EMPNO ENAME      JOBMGR HIREDATE  SAL    COMM     DEPTNO   CNT---------- ---------- ---------- ---------- -------------- ---------- ---------- ---------- ----------      7369 SMITH      CLERK       7902 17-12月-80  800 20     1      7499 ALLEN      SALESMAN       7698 20-2月 -81 1600     300 30     1      7521 WARD       SALESMAN       7698 22-2月 -81 1250     500 30     2      7566 JONES      MANAGER       7839 02-4月 -81 2975 20     1      7654 MARTIN     SALESMAN       7698 28-9月 -81 1250    1400 30     1      7698 BLAKE      MANAGER       7839 01-5月 -81 2850 30     1      7788 SCOTT      ANALYST       7566 09-12月-82 3000 20     1      7844 TURNER     SALESMAN       7698 08-9月 -81 1500       0 30     1      7876 ADAMS      CLERK       7788 12-1月 -83 1100 20     1      7900 JAMES      CLERK       7698 03-12月-81  950 30     1      7902 FORD       ANALYST       7566 03-12月-81 3000 20     1     EMPNO ENAME      JOBMGR HIREDATE  SAL    COMM     DEPTNO   CNT---------- ---------- ---------- ---------- -------------- ---------- ---------- ---------- ----------      7521 WARD       SALESMAN       7698 22-2月 -81 1250     500 30     2已选择12行。SQL> SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno,                    (SELECT COUNT(*) FROM emp b WHERE b.empno = emp.empno) AS cnt               FROM emp  2    3  ;     EMPNO ENAME      JOBMGR HIREDATE  SAL    COMM     DEPTNO   CNT---------- ---------- ---------- ---------- -------------- ---------- ---------- ---------- ----------      7369 SMITH      CLERK       7902 17-12月-80  800 20     1      7499 ALLEN      SALESMAN       7698 20-2月 -81 1600     300 30     1      7521 WARD       SALESMAN       7698 22-2月 -81 1250     500 30     1      7566 JONES      MANAGER       7839 02-4月 -81 2975 20     1      7654 MARTIN     SALESMAN       7698 28-9月 -81 1250    1400 30     1      7698 BLAKE      MANAGER       7839 01-5月 -81 2850 30     1      7782 CLARK      MANAGER       7839 09-6月 -81 2450 10     1      7788 SCOTT      ANALYST       7566 09-12月-82 3000 20     1      7839 KING       PRESIDENT     17-11月-81 5000 10     1      7844 TURNER     SALESMAN       7698 08-9月 -81 1500       0 30     1      7876 ADAMS      CLERK       7788 12-1月 -83 1100 20     1     EMPNO ENAME      JOBMGR HIREDATE  SAL    COMM     DEPTNO   CNT---------- ---------- ---------- ---------- -------------- ---------- ---------- ---------- ----------      7900 JAMES      CLERK       7698 03-12月-81  950 30     1      7902 FORD       ANALYST       7566 03-12月-81 3000 20     1      7934 MILLER     CLERK       7782 23-1月 -82 1300 10     1已选择14行。SQL>      SELECT v.empno, v.ename, v.cnt, emp.empno, emp.ename, emp.cnt  FROM (SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno,               /*这儿使用的语句就叫标量子查询，以主表返回每一行的值为条件进行查询*/               (SELECT COUNT(*) FROM v v2 WHERE v2.empno = v.empno) AS cnt          FROM v) v  FULL JOIN (SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno,                    (SELECT COUNT(*) FROM emp b WHERE b.empno = emp.empno) AS cnt               FROM emp) emp    ON (emp.empno = v.empno AND emp.cnt = v.cnt)WHERE (v.empno IS NULL OR emp.empno IS NULL);  2    3    4    5    6    7    8    9   10       EMPNO ENAME     CNT      EMPNO ENAME      CNT---------- ---------- ---------- ---------- ---------- ----------      7521 WARD        2      7521 WARD        2       7521 WARD1       7782 CLARK1       7839 KING1       7934 MILLER1已选择6行。
0 0