本帖最后由 kinghow 于 2016-7-29 16:13 编辑
drop table t_1;
drop table t_2;
create table t_1(c_1 number,c_2 number,c_3 number);
create table t_2(c_1 number,c_2 number);
insert into t_1 values( 1,1,1);
insert into t_1 values( 1,2,2);
insert into t_1 values( 1,2,3);
insert into t_2 values( 1,2);
insert into t_2 values( 2,2);
commit;
SELECT *
FROM t_1 a LEFT JOIN t_2 b ON a.c_1 = b.c_1;
C_1 C_2 C_3 C_1 C_2
---------- ---------- ---------- ---------- ----------
1 2 3 1 2
1 2 2 1 2
1 1 1 1 2
SELECT *
FROM t_1 a LEFT JOIN t_2 b ON 1=1 and a.c_1 = b.c_1;
C_1 C_2 C_3 C_1 C_2
---------- ---------- ---------- ---------- ----------
1 1 1 1 2
1 2 2 1 2
1 2 3 1 2
SELECT *
FROM t_1 a LEFT JOIN t_2 b ON a.c_1 = b.c_1 order by a.c_2;
C_1 C_2 C_3 C_1 C_2
---------- ---------- ---------- ---------- ----------
1 1 1 1 2
1 2 3 1 2
1 2 2 1 2
SELECT *
FROM t_1 a LEFT JOIN t_2 b ON 1=1 and a.c_1 = b.c_1 order by a.c_2;
C_1 C_2 C_3 C_1 C_2
---------- ---------- ---------- ---------- ----------
1 1 1 1 2
1 2 3 1 2
1 2 2 1 2
SELECT *
FROM t_1 a LEFT JOIN t_2 b ON a.c_1 = b.c_1 order by a.c_1;
C_1 C_2 C_3 C_1 C_2
---------- ---------- ---------- ---------- ----------
1 2 3 1 2
1 1 1 1 2
1 2 2 1 2
SELECT *
FROM t_1 a LEFT JOIN t_2 b ON 1=1 and a.c_1 = b.c_1 order by a.c_1;
C_1 C_2 C_3 C_1 C_2
---------- ---------- ---------- ---------- ----------
1 1 1 1 2
1 2 3 1 2
1 2 2 1 2
现实场景中不存在c_3字段,c_3字段在这里用来标识原表中记录顺序。
排序问题的解决:
SELECT *
FROM (select rowid rd,t1.* from t_1 t1) a LEFT JOIN t_2 b ON a.c_1 = b.c_1 order by a.rd;
RD C_1 C_2 C_3 C_1 C_2
------------------ ---------- ---------- ---------- ---------- ----------
AAQoXNAAIAAJT3PAAA 1 1 1 1 2
AAQoXNAAIAAJT3PAAB 1 2 2 1 2
AAQoXNAAIAAJT3PAAC 1 2 3 1 2
问:
1、为什么left join 会改变结果集中原表中记录顺序?
2、加了 1=1条件后,为什么能保持结果集原表顺序?
3、为什么,加了 order by 后,1=1失效?
4、为什么,order by c_1后出现新的变化?