select r.patient_id,r.patient_name from repository r where r.administrative_division='360827' --600条记录 union select m.patient_id,m.patient_name from medicalrecords m where m.administrative_division = '360827' --12条记录查出来是 710条记录
是不是你单查的时候表里没增加数据,当你union的时候 有人对表做操作 插入数据了?
union 会去掉重复的数据,试试union all...
去掉重复的数据都比以前的多了,如果用union all的话恐怕更多。
create table t2 as select * from emp where rownum<7;create table t2 as select * from emp where rownum<11;SQL> select rownum,empno,ename,job,sal from t1 2 union 3 select rownum,empno,ename,job,sal from t2; ROWNUM EMPNO ENAME JOB SAL ---------- ---------- ---------- --------- ---------- 1 7369 SMITH CLERK 800 2 7499 ALLEN SALESMAN 1600 3 7521 WARD SALESMAN 1250 4 7566 JONES MANAGER 2975 5 7654 MARTIN SALESMAN 1250 6 7698 BLAKE MANAGER 2850 7 7782 CLARK MANAGER 2450 8 7788 SCOTT ANALYST 3000 9 7839 KING PRESIDENT 5000 10 7844 TURNER SALESMAN 1500SQL> select rownum,empno,ename,job,sal from t1 2 union all 3 select rownum,empno,ename,job,sal from t2; ROWNUM EMPNO ENAME JOB SAL ---------- ---------- ---------- --------- ---------- 1 7369 SMITH CLERK 800 2 7499 ALLEN SALESMAN 1600 3 7521 WARD SALESMAN 1250 4 7566 JONES MANAGER 2975 5 7654 MARTIN SALESMAN 1250 6 7698 BLAKE MANAGER 2850 1 7369 SMITH CLERK 800 2 7499 ALLEN SALESMAN 1600 3 7521 WARD SALESMAN 1250 4 7566 JONES MANAGER 2975 5 7654 MARTIN SALESMAN 1250 6 7698 BLAKE MANAGER 2850 7 7782 CLARK MANAGER 2450 8 7788 SCOTT ANALYST 3000 9 7839 KING PRESIDENT 5000 10 7844 TURNER SALESMAN 1500union:将重复的行取出了 union all:将2个表中的数据放到一起,保留重复行
union取出的数据有重复的,建议LZ想想怎么去掉重复的数据或者想想把SQL改成其它的表示形式!
这么说.. 难道是在事务期间目标数据表有异动? 用只读事务试试: SET TRANSACTION READ ONLY;
select r.patient_id,r.patient_name from repository r where r.administrative_division='360827' --600条记录 union allselect m.patient_id,m.patient_name from medicalrecords m where m.administrative_division = '360827' --12条记录
select r.patient_id,r.patient_name from repository r where r.administrative_division='360827' --600条记录
union
select m.patient_id,m.patient_name from medicalrecords m where m.administrative_division = '360827' --12条记录查出来是 710条记录
create table t2 as
select * from emp
where rownum<7;create table t2 as
select * from emp
where rownum<11;SQL> select rownum,empno,ename,job,sal from t1
2 union
3 select rownum,empno,ename,job,sal from t2; ROWNUM EMPNO ENAME JOB SAL
---------- ---------- ---------- --------- ----------
1 7369 SMITH CLERK 800
2 7499 ALLEN SALESMAN 1600
3 7521 WARD SALESMAN 1250
4 7566 JONES MANAGER 2975
5 7654 MARTIN SALESMAN 1250
6 7698 BLAKE MANAGER 2850
7 7782 CLARK MANAGER 2450
8 7788 SCOTT ANALYST 3000
9 7839 KING PRESIDENT 5000
10 7844 TURNER SALESMAN 1500SQL> select rownum,empno,ename,job,sal from t1
2 union all
3 select rownum,empno,ename,job,sal from t2; ROWNUM EMPNO ENAME JOB SAL
---------- ---------- ---------- --------- ----------
1 7369 SMITH CLERK 800
2 7499 ALLEN SALESMAN 1600
3 7521 WARD SALESMAN 1250
4 7566 JONES MANAGER 2975
5 7654 MARTIN SALESMAN 1250
6 7698 BLAKE MANAGER 2850
1 7369 SMITH CLERK 800
2 7499 ALLEN SALESMAN 1600
3 7521 WARD SALESMAN 1250
4 7566 JONES MANAGER 2975
5 7654 MARTIN SALESMAN 1250
6 7698 BLAKE MANAGER 2850
7 7782 CLARK MANAGER 2450
8 7788 SCOTT ANALYST 3000
9 7839 KING PRESIDENT 5000
10 7844 TURNER SALESMAN 1500union:将重复的行取出了
union all:将2个表中的数据放到一起,保留重复行
union取出的数据有重复的,建议LZ想想怎么去掉重复的数据或者想想把SQL改成其它的表示形式!
用只读事务试试: SET TRANSACTION READ ONLY;
union allselect m.patient_id,m.patient_name from medicalrecords m where m.administrative_division = '360827' --12条记录