关于一个表中的字段拆分后与另一个表关系的问题

Question

表A中有四个字段，一个docid，一个文章标题，另两个col1和col2，分别存储的是附件的采集时间和文件大小，以分号分开，如下：
表A：
docid: 213
doctitle: 国际标准FT
col1:    2009-03-24;2009-01-14;2009-06-24;2009-01-16
col2:    34568;34267;23685;31256表B中存放的是表A中的附件，以docid与表A关联，且入库的appid顺序跟表A中的时间字段分隔开的顺序是一样的，即表A中col1中这四个时间拆分开分别对应表B中appid是1,2,3,4的行表B：
appid docid   filename
1      213    201923.zip
2      213    2159871.pdf
3      213    p4569.word
4      213    25694.pdf现想将表A跟表B关系起来，形成文件大小，时间还有文件名对应，即下面的效果，要如何处理？(数据库结果是定好的，不能修改)appid  docid  filename       col1         col2
1      213    201923.zip   2009-03-24     34568
2      213    2159871.pdf  2009-01-14     34267
3      213    p4569.word   2009-06-24     23685
4      213    25694.pdf    2009-01-16     31256

Answer 1

with a as(
 select 213 docid, '国际标准FT' doctitle, 
 '2009-03-24;2009-01-14;2009-06-24;2009-01-16' col1,
 '34568;34267;23685;31256' col2
 from dual
),
b as(
 select 1 appid,213 docid,'201923.zip' filename from dual
 union all
 select 2,213,'2159871.pdf' from dual
 union all
 select 3,213,'p4569.word' from dual
 union all
 select 4,213,'25694.pdf' from dual
),
c as(
 select b.*, row_number() over (partition by docid order by appid) rn from b
)
select c.appid, c.docid, c.filename,
regexp_substr(a.col1,'\d{1,4}-\d{1,2}-\d{1,2}',1,c.rn) col1, 
regexp_substr(a.col2,'\d{1,}',1,c.rn) col2
from a,c
where a.docid=c.docid;
/*
1 213 201923.zip 2009-03-24 34568
2 213 2159871.pdf 2009-01-14 34267
3 213 p4569.word 2009-06-24 23685
4 213 25694.pdf 2009-01-16 31256
*/

Answer 2

select c.appid, c.docid, c.filename,
substr(a.col1,instr(';'||a.col1,';',1,c.rn),instr(a.col1||';',';',1,c.rn)-instr(';'||a.col1,';',1,c.rn)) col1, 
substr(a.col2,instr(';'||a.col2,';',1,c.rn),instr(a.col2||';',';',1,c.rn)-instr(';'||a.col2,';',1,c.rn)) col2
from a,c
where a.docid=c.docid;

Answer 3

楼上的，c.rn 这个代表什么

Answer 4

-- 方法 2
with a as(
 select 213 docid, '国际标准FT' doctitle, 
 '2009-03-24;2009-01-14;2009-06-24;2009-01-16' col1,
 '34568;34267;23685;31256' col2
 from dual
),
b as(
 select 1 appid,213 docid,'201923.zip' filename from dual
 union all
 select 2,213,'2159871.pdf' from dual
 union all
 select 3,213,'p4569.word' from dual
 union all
 select 4,213,'25694.pdf' from dual
),
c as(
 select b.*, 
 row_number() over (partition by docid order by appid) rn -- 与 col1 和 col2 中内容的对应关系
 from b
)
select c.appid, c.docid, c.filename,
substr(a.col1,instr(';'||a.col1,';',1,c.rn),instr(a.col1||';',';',1,c.rn)-instr(';'||a.col1,';',1,c.rn)) col1, 
substr(a.col2,instr(';'||a.col2,';',1,c.rn),instr(a.col2||';',';',1,c.rn)-instr(';'||a.col2,';',1,c.rn)) col2
from a,c
where a.docid=c.docid;

Answer 5

SQL> edi
已写入 file afiedt.buf  1  select b.appid,a.docid,b.filename,
  2  substr(a.col1,instr(a.col1,';',1,b.rn)-10,10) col1,
  3  substr(a.col2,instr(a.col2,';',1,b.rn)-5,5) col2
  4  from a,
  5  (select appid,filename,docid,row_number() over(partition by docid order by appid) rn from b) b
  6* where a.docid=b.docid
SQL> /     APPID      DOCID FILENAME             COL1                 COL2
---------- ---------- -------------------- -------------------- ----------
         1        213 201923.zip           2009-03-24           34568
         2        213 2159871.pdf          2009-01-14           34267
         3        213 p4569.word           2009-06-24           23685
         4        213 25694.pdf            2009-01-16           31256SQL> 

Answer 6

完善下SQL> select b.appid,a.docid,b.filename,
  2  substr(a.col1,instr(';'||a.col1,';',1,b.rn),
  3  instr(a.col1||';',';',1,b.rn)-instr(';'||a.col1,';',1,b.rn)) col1,
  4  substr(a.col2,instr(';'||a.col2,';',1,b.rn),
  5  instr(a.col2||';',';',1,b.rn)-instr(';'||a.col2,';',1,b.rn)) col2
  6  from a,
  7  (select appid,filename,docid,row_number() over(partition by docid order by appid) rn from b) b
  8  where a.docid=b.docid
  9  /     APPID      DOCID FILENAME             COL1                 COL2
---------- ---------- -------------------- -------------------- --------------------
         1        213 201923.zip           2009-03-24           34568
         2        213 2159871.pdf          2009-01-14           34267
         3        213 p4569.word           2009-06-24           23685
         4        213 25694.pdf            2009-01-16           31256