table1
a b c d e f
1 1 1 0 aa x
1 1 1 0 bb x
1 1 1 0 cc z
1 1 1 1 aa z
1 1 1 1 bb y
1 1 1 1 cc y
table2
f g
x ssss
y gggg
z wwww
查询结果为
a b c d e f seqno
1 1 1 0 aa ssss 1
1 1 1 0 bb ssss 2
1 1 1 0 cc wwww 3
1 1 1 1 aa wwww 1
1 1 1 1 bb gggg 2
1 1 1 1 cc gggg 3
根据table1中字段 a,b,b,c来编号,只要其中一个改变后,编号重新从1开始
a b c d e f
1 1 1 0 aa x
1 1 1 0 bb x
1 1 1 0 cc z
1 1 1 1 aa z
1 1 1 1 bb y
1 1 1 1 cc y
table2
f g
x ssss
y gggg
z wwww
查询结果为
a b c d e f seqno
1 1 1 0 aa ssss 1
1 1 1 0 bb ssss 2
1 1 1 0 cc wwww 3
1 1 1 1 aa wwww 1
1 1 1 1 bb gggg 2
1 1 1 1 cc gggg 3
根据table1中字段 a,b,b,c来编号,只要其中一个改变后,编号重新从1开始
from t1,t2
where t1.f=t2.f
(select t1.a a,t1.b b,t1.c c,t1.d d,t1.e e,t2.g f,
t1.a||t1.b||t1.c||t1.d seqno
from table1 t1,table2 t2
where t1.f=t2.f) a
select * from
(select t1.a a,t1.b b,t1.c c,t1.d d,t1.e e,t2.g f,
row_number() over(partition by t1.a | |t1.b | |t1.c | |t1.d order by t1.a | |t1.b | |t1.c | |t1.d) seqno
from table1 t1,table2 t2
where t1.f=t2.f) a
from table1, table2
where table2.f = table1.f;