基于另一个表中的序列从另一个中获取序列字段

Question

我有两张表表1

SETID  CRSE_ID COMPONENT CAMPUS 
A      10001   LAB        C1
A      10002   LEC        C1
A      10003   LAB        C1
A      10004   LAB        C1

表2

SETID  ITEM TYPE      DESCR   CAMPUS
A      300000220010   Book    C1
A      300000220020   Book    C2
A      300000220030   Book    C1
A      300000220040   Book    C2
A      300000220050   Book    C1
A      300000220060   Book    C2
A      300000220070   Book    C1

我想得到一个结果，其中项目类型被映射到每一行

SETID  CRSE_ID COMPONENT CAMPUS ITEM_TYPE
A      10001   LAB        C1    300000220010   
A      10001   LEC        C1    300000220030   
A      10001   LAB        C1    300000220050   
A      10001   LAB        C1    300000220070 
A      10002   LAB        C1    300000220010   
A      10002   LEC        C1    300000220030   
A      10002   LAB        C1    300000220050   
A      10002   LAB        C1    300000220070
A      10003   LAB        C1    300000220010   
A      10003   LEC        C1    300000220030   
A      10003   LAB        C1    300000220050   
A      10003   LAB        C1    300000220070
A      10004   LAB        C1    300000220010   
A      10004   LEC        C1    300000220030   
A      10004   LAB        C1    300000220050   
A      10004   LAB        C1    300000220070   

项目类型的顺序随着新CRSE_ID的出现而不断增加。项目类型以校园C1 的赔率结束

Answer 1

I认为您只需要添加一个序列号，并将其用作join条件之一：

select t1.*, t2.item_type
from (select t1.*, row_number() over (partition by setid, campus order by setid) as seqnum
from table1 t1
) t1 left join
(select t2.*, row_number() over (partition by setid, campus order by setid) as seqnum
from table2 t2
) t2
on t1.setid = t2.setid and t1.campus = t2.campus and t1.seqnum = t2.seqnum;