我有两个表 Table1
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 不断增加。Campus C1 的项目类型以 Odd 结尾
我认为您只想添加一个序列号并将其用作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;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句