求助一个oracle sql语句

Question

我将如下文本导入到一个表中，该表只有一列。以下斜体表内数据：用户ID：1
老师
学校所在地：北京
用户ID：2
公司职员
公司名称：某某公司
工作地点：上海
用户ID：3
公务员
事业单位：某某办事处
毕业院校：某某学校
党龄：某某数字下面的记录行 都是按照上述三类格式重复的
我想将三类职业分类，得到如下表格：
老师一类的放一张表t_teacher，公司职员的放一张表t_worker，公务员的放一张表t_agent。
t_teacher 结构就是 2列，为 用户ID，学校所在地。
t_worker  结构就是 3列，为 用户ID，公司名称，工作地点。
t_agent   结构就是 2列，为 用户ID，事业单位，毕业院校，党龄。请高手指点一下如何写这样的sql语句呢，谢谢了。

Answer 1

写一个存储过程
SELECT COLUMN FROM 目标表取出所有的列
然后每12行做循环
循环中分别像三个表中INSERT 你要的数据 

Answer 2

这个表也太奇怪了。格式像上面列的那样？
 教师的
insert into t_teacher 
  select to_number(a),b from(select col1,lag(substr(col1,instr(col1,'：')+1))over(order by rownum)a,
    lead(substr(col1,instr(col1,'所在地：')+4))over(order by rownum)b
  from table1)
  where col1='老师'其他两个也类似

Answer 3

已经解决了，并附有建表语句。
create table qc_col(col varchar2(60)) tablespace bidm_data;insert into qc_col values('用户ID：1');
insert into qc_col values('老师');
insert into qc_col values('学校所在地：北京');
insert into qc_col values('用户ID：2');
insert into qc_col values('公司职员');
insert into qc_col values('公司名称：某某公司');
insert into qc_col values('工作地点：上海');
insert into qc_col values('用户ID：3');
insert into qc_col values('公务员');
insert into qc_col values('事业单位：某某办事处');
insert into qc_col values('毕业院校：某某学校');
insert into qc_col values('党龄：某某数字');create table t_teacher(usr_id varchar2(20),address varchar2(60))tablespace bidm_data;insert into t_teacher
with ss as (
select col,rn,connect_by_root col col_root
  from (
select col,rownum rn
  from qc_col )
 start with col like '用户ID%'
connect by prior rn=rn-1 and col not like '用户ID%' )
select col_root,max(case when rn_2=3 then col end)
  from (
select ss.col,a.col_root,row_number()over(partition by ss.col_root order by ss.rn) rn_2
  from (
select col_root
  from (
select col,col_root,row_number()over(partition by col_root order by rn) rn_int
  from ss )
 where rn_int=2 and col='老师' ) a,ss
where a.col_root=ss.col_root )
 group by col_root
;select * from t_teachercreate table t_worker(usr_id varchar2(20),cmp_nam varchar2(60),work_spc varchar2(60))tablespace bidm_data;
insert into t_worker
with ss as (
select col,rn,connect_by_root col col_root
  from (
select col,rownum rn
  from qc_col )
 start with col like '用户ID%'
connect by prior rn=rn-1 and col not like '用户ID%' )
select col_root,max(case when rn_2=3 then col end),max(case when rn_2=4 then col end)
  from (
select ss.col,a.col_root,row_number()over(partition by ss.col_root order by ss.rn) rn_2
  from (
select col_root
  from (
select col,col_root,row_number()over(partition by col_root order by rn) rn_int
  from ss )
 where rn_int=2 and col='公司职员' ) a,ss
where a.col_root=ss.col_root )
 group by col_root
;select * from t_workercreate table t_agent(usr_id varchar2(20),cmp_nam varchar2(60),unvst varchar2(60),pty_age varchar2(60))tablespace bidm_data;
insert into t_agent
with ss as (
select col,rn,connect_by_root col col_root
  from (
select col,rownum rn
  from qc_col )
 start with col like '用户ID%'
connect by prior rn=rn-1 and col not like '用户ID%' )
select col_root,max(case when rn_2=3 then col end),max(case when rn_2=4 then col end),max(case when rn_2=5 then col end)
  from (
select ss.col,a.col_root,row_number()over(partition by ss.col_root order by ss.rn) rn_2
  from (
select col_root
  from (
select col,col_root,row_number()over(partition by col_root order by rn) rn_int
  from ss )
 where rn_int=2 and col='公务员' ) a,ss
where a.col_root=ss.col_root )
 group by col_root
;select * from t_agent;

Answer 4

SQL> select * from t_teacher;USR_ID               ADDRESS
-------------------- ------------------------------------------------------------
用户ID：1            学校所在地：北京SQL> select * from t_worker;USR_ID               CMP_NAM                                                      WORK_SPC
-------------------- ------------------------------------------------------------ ------------------------------------------------------------
用户ID：2            公司名称：某某公司                                           工作地点：上海SQL> select * from t_agent;USR_ID               CMP_NAM                                                      UNVST
-------------------- ------------------------------------------------------------ ------------------------------------------------------------
PTY_AGE
------------------------------------------------------------
用户ID：3            事业单位：某某办事处                                         毕业院校：某某学校
党龄：某某数字

Answer 5

如果要分离:的前面和后面，用instr和substr可以实现。
substr(col,instr(col,':')+1)可以截取：后的字符串出来。

Answer 6

这种方法也很好，比我的效率高。
不过缺点是如果公务员不是5条记录，而在实际数据中只有4条的话，就会挪位了。
我的是从一个“用户ID”到下一个“用户ID”，效率较低。

Answer 7

create or replace procedure p_test
is
id int;
cursor myCursor is select content from tb_test order by rownum;
strSQL varchar2(2000);
begin
for cur in myCursor
loop
if cur.content like '用户ID：%' then
id := to_number(replace(cur.content,'用户ID：',''));
strSQL := '';
end if;
if cur.content = '老师' then
strSQL := 'INSERT INTO t_teacher VALUES ('|| id || ',''';
end if;
if cur.content = '公司职员' then
strSQL := 'INSERT INTO t_worker VALUES ('|| id || ',''';
end if;
if cur.content = '公务员' then
strSQL := 'INSERT INTO t_agent VALUES ('|| id || ',''';
end if;
if cur.content like '学校所在地：%' then
strSQL := strSQL || replace(cur.content,'学校所在地：','') || ''')';
end if;
if cur.content like '公司名称：%' then
strSQL := strSQL || replace(cur.content,'公司名称：','') || ''',''';
end if;
if cur.content like '工作地点：%' then
strSQL := strSQL || replace(cur.content,'工作地点：','') || ''')';
end if;
if cur.content like '事业单位：%' then
strSQL := strSQL || replace(cur.content,'事业单位：','') || ''',''';
end if;
if cur.content like '毕业院校：%' then
strSQL := strSQL || replace(cur.content,'毕业院校：','') || ''',''';
end if;
if cur.content like '党龄：%' then
strSQL := strSQL || replace(cur.content,'党龄：','') || ''')';
end if;
if strSQL like '%)' then
execute immediate strSQL;
end if;
end loop;
end;
/写了一个过程，方法比较笨。

Answer 8

16:25:10 scott@TUNGKONG> create table tb_test (content) as select '用户ID：1' from dual union all
16:25:19   2  select '老师' from dual union all
16:25:19   3  select '学校所在地：北京' from dual union all
16:25:19   4  select '用户ID：2' from dual union all
16:25:19   5  select '公司职员' from dual union all
16:25:19   6  select '公司名称：某某公司' from dual union all
16:25:19   7  select '工作地点：上海' from dual union all
16:25:19   8  select '用户ID：3' from dual union all
16:25:19   9  select '公务员' from dual union all
16:25:19  10  select '事业单位：某某办事处' from dual union all
16:25:19  11  select '毕业院校：某某学校' from dual union all
16:25:19  12  select '党龄：某某数字' from dual;表已创建。已用时间:  00: 00: 00.01
16:25:19 scott@TUNGKONG> create table t_teacher (用户ID int,学校所在地 varchar2(30));表已创建。已用时间:  00: 00: 00.10
16:25:19 scott@TUNGKONG> create table t_worker (用户ID int,公司名称 varchar2(30),学校所在地 varchar2(30));表已创建。已用时间:  00: 00: 00.00
16:25:19 scott@TUNGKONG> create table t_agent (用户ID int,事业单位 varchar2(30),毕业院校 varchar2(30),党龄 varchar2(30));表已创建。已用时间:  00: 00: 00.06
16:25:19 scott@TUNGKONG> 
16:25:19 scott@TUNGKONG> create or replace procedure p_test
16:25:19   2  is
16:25:19   3   id int;
16:25:19   4   cursor myCursor is select content from tb_test order by rownum;
16:25:19   5   strSQL varchar2(2000);
16:25:19   6  begin
16:25:19   7   for cur in myCursor
16:25:19   8   loop
16:25:19   9   if cur.content like '用户ID：%' then
16:25:19  10   id := to_number(replace(cur.content,'用户ID：',''));
16:25:19  11   strSQL := '';
16:25:19  12   end if;
16:25:19  13   if cur.content = '老师' then
16:25:19  14   strSQL := 'INSERT INTO t_teacher VALUES ('|| id || ',''';
16:25:19  15   end if;
16:25:19  16   if cur.content = '公司职员' then
16:25:19  17   strSQL := 'INSERT INTO t_worker VALUES ('|| id || ',''';
16:25:19  18   end if;
16:25:19  19   if cur.content = '公务员' then
16:25:19  20   strSQL := 'INSERT INTO t_agent VALUES ('|| id || ',''';
16:25:19  21   end if;
16:25:19  22   if cur.content like '学校所在地：%' then
16:25:19  23   strSQL := strSQL || replace(cur.content,'学校所在地：','') || ''')';
16:25:19  24   end if;
16:25:19  25   if cur.content like '公司名称：%' then
16:25:19  26   strSQL := strSQL || replace(cur.content,'公司名称：','') || ''',''';
16:25:19  27   end if;
16:25:19  28   if cur.content like '工作地点：%' then
16:25:19  29   strSQL := strSQL || replace(cur.content,'工作地点：','') || ''')';
16:25:19  30   end if;
16:25:19  31   if cur.content like '事业单位：%' then
16:25:19  32   strSQL := strSQL || replace(cur.content,'事业单位：','') || ''',''';
16:25:19  33   end if;
16:25:19  34   if cur.content like '毕业院校：%' then
16:25:19  35   strSQL := strSQL || replace(cur.content,'毕业院校：','') || ''',''';
16:25:19  36   end if;
16:25:19  37   if cur.content like '党龄：%' then
16:25:19  38   strSQL := strSQL || replace(cur.content,'党龄：','') || ''')';
16:25:19  39   end if;
16:25:19  40   if strSQL like '%)' then
16:25:19  41   execute immediate strSQL;
16:25:19  42   end if;
16:25:19  43   end loop;
16:25:19  44  end;
16:25:19  45  /过程已创建。已用时间:  00: 00: 00.04
16:25:20 scott@TUNGKONG> select * from tb_test;CONTENT
--------------------
用户ID：1
老师
学校所在地：北京
用户ID：2
公司职员
公司名称：某某公司
工作地点：上海
用户ID：3
公务员
事业单位：某某办事处
毕业院校：某某学校
党龄：某某数字已选择12行。已用时间:  00: 00: 00.04
16:25:27 scott@TUNGKONG> select * from t_teacher;未选定行已用时间:  00: 00: 00.01
16:25:38 scott@TUNGKONG> select * from t_worker;未选定行已用时间:  00: 00: 00.01
16:25:44 scott@TUNGKONG> select * from t_agent;未选定行已用时间:  00: 00: 00.00
16:25:48 scott@TUNGKONG> exec p_test;PL/SQL 过程已成功完成。已用时间:  00: 00: 00.01
16:25:53 scott@TUNGKONG> select * from t_teacher;    用户ID 学校所在地
---------- ------------------------------
         1 北京已用时间:  00: 00: 00.01
16:25:56 scott@TUNGKONG> select * from t_worker;    用户ID 公司名称                       学校所在地
---------- ------------------------------ ------------------------------
         2 某某公司                       上海已用时间:  00: 00: 00.01
16:25:59 scott@TUNGKONG> select * from t_agent;    用户ID 事业单位                       毕业院校
---------- ------------------------------ ------------------------------
党龄
------------------------------
         3 某某办事处                     某某学校
某某数字
已用时间:  00: 00: 00.03

Answer 9

受万神的启发
写个工人的insert into t_worker
with t as(select connect_by_root col1 flag,col1
  from (select col1,rownum rn from table1) 
  start with col1 like '用户id%'
  connect by prior rn=rn-1
    and col1 not like '用户id%')
select to_number(substr(a.flag,6)),substr(b.col1,6),substr(c.col1,6) from 
(select * from t where col1 = '公司职员') a,
(select * from t where col1 like '公司名称%') b,
(select * from t where col1 like '工作地点%') c
where a.flag=b.flag(+)
  and a.flag=c.flag(+)

Answer 10

insert into t_workerwith t as(select connect_by_root col1 flag,col1
  from (select col1,rownum rn from table1) 
  start with col1 like '用户id%'
  connect by prior rn=rn-1
    and col1 not like '用户id%')select to_number(substr(c.flag,6)),max(case when c.col1 like '公司名称%' then substr(c.col1,6) end),
  max(case when c.col1 like '工作地点%' then substr(c.col1,6) end) from 
t c
where exists(select 1 from t where flag=c.flag and col1='公司职员')
group by c.flag