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(+)一我没测试过,二我没考虑效率问题,呵呵,见笑了。
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)