select t1.a,....,tt.descr
from t1,(select descr,f1,f2 from t2 where comment_id in
(select max(comment_id) from t2 group by f1,f2)) tt
where t1.f1 = tt.f1(+)
and t1.f2 = tt.f2(+)一我没测试过,二我没考虑效率问题,呵呵,见笑了。
from t1,(select descr,f1,f2 from t2 where comment_id in
(select max(comment_id) from t2 group by f1,f2)) tt
where t1.f1 = tt.f1(+)
and t1.f2 = tt.f2(+)一我没测试过,二我没考虑效率问题,呵呵,见笑了。
解决方案 »
- Oracle开发中局部索引与全局索引有什么作用和区别
- oracle进行主备数据库切换
- ORA-01460: 转换请求无法实施或不合理
- Oracle存储过程的参数溢出该怎么办?
- .net中怎么样来调用包中的函数?
- sql语句求助
- 请教:在SQLPLUS中如何查询空间数据,怎样才能识别CONTAINS、DISTANCE等空间分析
- oracle问题
- 无法使用enterprise manager
- 如何实现sql数据库导入Orcal8i数据库
- 请问pl/sql的fuction的编写能否使用数组或游标作为传入参数和传出参数??
- Oracle高手看過來﹐根據動態SQL返回一個Ref Cursor,高分求救!
select t1.a,...,t2.descr
from t1,t2,(select f1,f2,max(t.comment_id) comment_id1 from t2 group by f1,f2) t3
where t1.f1 = t2.f1(+)
and t1.f2 = t2.f2(+)
and t1.f1=t3.f1(+)
and t1.f2=t3.f2(+)
and t2.comment_id = t3.comment_id1(+)
select t1.a,...,t2.descr
from t1,t2
where t1.f1 = t2.f1(+)
and t1.f2 = t2.f2(+)
and t2.comment_id(+) = (select max(t.comment_id) from t2 t where t.f1 = t2.f1 and t.f2 = t2.f2)
要是我写这个SQL就这么写
select t1.a,...,(SELECT A.descr FROM T2 A where A.comment_id =
(select max(comment_id) from t2 B WHERE t.f1 = B.f1 and t.f2 = B.f2))
from t1
呵呵,好象效率不高,而且看起来也比snowy_howe(天下有雪)兄的复杂。见笑
ORA-01417: a table may be outer joined to at most one other tableTO:hillhx(曾经的曾经) 在t2.comment_id的后面加个(+),ORACLE会提示
ORA-01799: a column may not be outer-joined to a subquery
按老兄后面写的执行,很不巧的是,我的T2.descr是LONG类型的,所以会高亮显示A.descr,并得到提示
ORA-00997: illegal use of LONG datatype
请问各位还有其他办法吗?
from t1,t2
where t1.f1 = t2.f1(+)
and t1.f2 = t2.f2(+)
and (t2.comment_id = (select max(t.comment_id) from t2 t where t.f1 = t2.f1 and t.f2 = t2.f2) or t2.comment_id is null)