try:
select * from
(select tb_a.*,row_number() over(partition by f2 order by f1) r1 from tb_a) t,
(select tb_b.*,row_number() over(partition by f4 order by f3) r1 from tb_b) tt
where t.r1=tt.r2 and t.f1=tt.f3;
select * from
(select tb_a.*,row_number() over(partition by f2 order by f1) r1 from tb_a) t,
(select tb_b.*,row_number() over(partition by f4 order by f3) r1 from tb_b) tt
where t.r1=tt.r2 and t.f1=tt.f3;
F1 F2 F5 F3 F4
A 10 F x 10
B 20 F y 10
C 20 F z 20
D 30 F利用关联值进行更新要怎么写?
即
表A 表B
F1 F2 F5 F3 F4
A 10 T x 10
B 20 T y 10
C 20 F z 20
D 30 F
即对于表A的F2字段,如果对于表B有相同大小的字段,那么逐一匹配,分别做上记号.
比如表A,
对于10,表只有一条记录,表B虽然有两条F4为10的记录比表A多,但是只需要对表A这条做标记
对于20,表有两条记录,在表B中只有一条F4为20记录,则在F2为20的记录上任意一条上做标记
exists(select 1 from (select min(f1) f1,f2 from a group by f2) tt where tt.f1=a.f1
and tt.f2=a.f2);
select f1,f2,f3,f4 from
(select tmp.*,row_number() over(partition by f2 order by f1) rn
from (select a.*,b.* from a,b) tmp where a.f2 = b.f4)
where rn = 1
如果是随机关联可以这样,不过效率不高
select f1,f2,f3,f4 from
(select tmp.*,row_number() over(partition by f2 order by dbms_random.random()) rn
from (select a.*,b.* from a,b) tmp where a.f2 = b.f4)
where rn = 1
比如如果发生下面的情况:
表A 表B
F1 F2 F5 F3 F4
A 10 F x 10
B 10 F y 10
C 10 F z 20
D 30 F
因为对于表B有两条为10的F4字段,则应该给表A的两条为10的F2字段做上标记.处理的结果就是
表A 表B
F1 F2 F5 F3 F4
A 10 T x 10
B 10 T y 10
C 10 F z 20
D 30 F你的取最小值的方法并不适用.
如果我能利用COUNT判断表B的某个值记录比A多,我就把A该值的所有记录做标记;
如果我能判断表A的某个值记录比B多,那么我就把A该前N(N为表B该值记录的个)个记录做标记;因此需要用到GROUP和ROWNUM...具体写法还在琢磨中...
select c.*,d.* from
(select a.*,row_number() over(partition by f2 order by dbms_random.random()) row_id
from a) c,(select b.*,row_number() over(partition by f4 order by f3) row_id
from a) d
where c.row_id = d.row_id更新:
update a set f5 = 'T' where exists
(select 1 from
(select a.*,row_number() over(partition by f2 order by dbms_random.random()) row_id from a) c,
(select b.*,row_number() over(partition by f4 order by f3) row_id from a) d
where c.row_id = d.row_id and a.f1 = c.f1 and a.f2 = c.f2
)