select t1.data data1, t2.data data2 from
(
select t.*, trunc((rownum - 1) / 2) x, mod(rownum - 1, 2) y from (select 1 data from dual union all select 2 data from dual union all select 3 data from dual) t
) t1
left join
(
select t.*, trunc((rownum - 1) / 2) x, mod(rownum - 1, 2) y from (select 1 data from dual union all select 2 data from dual union all select 3 data from dual) t
) t2 on t1.x = t2.x
where t1.y = 0 and t2.y = 1 其中“select t.*, trunc((rownum - 1) / 2) x, mod(rownum - 1, 2) y from (select 1 data from dual union all select 2 data from dual union all select 3 data from dual) t”返回的是一个这样的结果集:
Data X Y
1 0 0
2 0 1
3 1 0理论上是肯定有结果的,不知道是否和rownum有关系,因为把mod(rownum - 1, 2)换成实际的整数值就会有结果
(
select t.*, trunc((rownum - 1) / 2) x, mod(rownum - 1, 2) y from (select 1 data from dual union all select 2 data from dual union all select 3 data from dual) t
) t1
left join
(
select t.*, trunc((rownum - 1) / 2) x, mod(rownum - 1, 2) y from (select 1 data from dual union all select 2 data from dual union all select 3 data from dual) t
) t2 on t1.x = t2.x
where t1.y = 0 and t2.y = 1 其中“select t.*, trunc((rownum - 1) / 2) x, mod(rownum - 1, 2) y from (select 1 data from dual union all select 2 data from dual union all select 3 data from dual) t”返回的是一个这样的结果集:
Data X Y
1 0 0
2 0 1
3 1 0理论上是肯定有结果的,不知道是否和rownum有关系,因为把mod(rownum - 1, 2)换成实际的整数值就会有结果
解决方案 »
- oracle 部分求和判断
- 如何入门ORACLE数据库开发
- 差一个条件,执行速度相差10倍,请教?left join用法是否存在问题?
- pl sql developer 登录数据库没有反应 急急急急
- oracle 性能的优化
- ORACLE 9i 的DATE 型字段中出现了"2006-12-11 下午 08:04:04"
- 这个sql转换函数怎么写,再现等
- 那位兄弟知道Oracle9i的复制管理员用户名和密码是什么?
- 求教一个sql语句,想了一天了
- oracle9i for solaris sprac怎么刻
- 请大家来帮我看下这段SQL语句吧(怎么在小于1%前面添加0的问题)
- 有关 oracle procedures 游标 语法问题
没有符合条件的记录
条件限制后,t1得到记录1,3;t2得到记录2,所以没有结果
select t1.data data1, t2.data data2, t1.Y Y1, t2.Y Y2 from
(
select t.*, trunc((rownum - 1) / 2) x, mod(rownum - 1, 2) y from (select 1 data from dual union all select 2 data from dual union all select 3 data from dual) t
) t1
left join
(
select t.*, trunc((rownum - 1) / 2) x, mod(rownum - 1, 2) y from (select 1 data from dual union all select 2 data from dual union all select 3 data from dual) t
) t2 on t1.x = t2.x返回结果:
data1 data2 y1 y2
2 1 1 0
1 1 0 0
2 2 1 1
1 2 0 1
3 3 0 0对这个结果再进行一次子查询,让条件y1=0,y2=1,会没有结果?
from
(
select t.*, trunc((rownum - 1) / 2) x, mod(rownum - 1, 2) y from (select 1 data from dual union all select 2 data from dual union all select 3 data from dual) t
) t1,
(
select t.*, trunc((rownum - 1) / 2) x, mod(rownum - 1, 2) y from (select 1 data from dual union all select 2 data from dual union all select 3 data from dual) t
) t2
where t1.x = t2.x(+) and t1.y = 0 and t2.y = 1
2 1 1 0
1 1 0 0
2 2 1 1
1 2 0 1
3 3 0 0还是不明白在这个结果集里where y1=0 and y2=1为什么是永false的条件?
(+)放在左边就是left join 放在右边就是right join
create table dali.test1(a int,b int);
create table dali.test2(a int,b int);
insert into dali.test1 values(1,456);
insert into dali.test1 values(2,427);
insert into dali.test2 values(1,45456);
insert into dali.test2 values(3,45656);
---内连接
select * from dali.test1 a, dali.test2 b where a.a=b.a;
---左连接
select * from dali.test1 a, dali.test2 b where a.a=b.a(+);
---右连接
select * from dali.test1 a, dali.test2 b where a.a(+)=b.a;
---完全连接
select * from dali.test1 a, dali.test2 b where a.a=b.a(+)
union
select * from dali.test1 a, dali.test2 b where a.a(+)=b.a;
---迪卡尔
select * from dali.test1, dali.test2;
'==================================================================
9i和sqlserver一样 left join,right join,full join
SQL> select t1.data data1, t2.data data2 from
(
select t.*, trunc((rownum - 1) / 2) x, mod(rownum - 1, 2) y from (select 1 data from dual union all select 2 data from dual union all select 3 data from dual) t
) t1
left join
(
select t.*, trunc((rownum - 1) / 2) x, mod(rownum - 1, 2) y from (selec 2 t 1 data from dual union all select 2 data from dual union all select 3 3 data from dual) t
) t2 on t1.x = t2.x
where t1.y = 0 and t2.y = 1
4 5 6 7 8 9 10
SQL> / DATA1 DATA2
---------- ----------
1 2SQL> select * from v$version;BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - ProductionSQL>有结果的,楼主是9i的某个版本吧,可能是小bug
避免起来也方便,用+,而不用left join就行了
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production可能真的是9i的bug,我同事说10g上确实可以。结帖 -_-