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

acruukt9 于 2021-07-27 发布在 Java

关注(0)|答案(1)|浏览(426)

我有两张table

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

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

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

项目类型的顺序随着新客户id的增加而增加。项目类型以奇数结尾

sql oracle dense-rank row-number

来源：https://stackoverflow.com/questions/63171372/getting-sequential-field-from-another-based-on-sequence-from-another-table

1条答案

按热度按时间

t9eec4r01#

我认为您只需要添加一个序列号，并将其用作 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;

赞(0）回复(0）举报 2021-07-27

我来回答

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

1条答案

相关问题

热门标签

最新问答