帮忙看个视图怎么写

Question

XH ITEMS
-- ---------
1  A1;A2;A3;
2  B1;B2;上面是表的2个字段，怎样通过查询语句得到下面的结果XH ITEM
-- ----
1  A1
1  A2
1  A3
2  B1
2  B2

Answer 1

SQL> select * from tt;
 
        XH ITEMS
---------- ---------
         1 A1;A2;A3;
         2 B1;B2;SQL> create or replace view v_tt as
  2  SELECT distinct
  3  xh,
  4  substr(ITEMS,3*(LEVEL-1)+1,2) ITEM
  5  FROM
  6  tt
  7  CONNECT BY LEVEL <= (length(ITEMS)/3)
  8  order by xh
  9  /
 
View createdSQL> select * from v_tt;
 
        XH ITEM
---------- ----
         1 A1
         1 A2
         1 A3
         2 B1
         2 B2

Answer 2

SQL> 
SQL> select XH,
  2         row_number() over(partition by XH order by ITEMS_) SEQ,
  3         substr(ITEMS_, - (length(ITEMS_) - instr(ITEMS_, ';', -1))) ITEM
  4    from (select XH, substr(ITEMS, 1, instr(ITEMS, ';', 1, rownum) - 1) ITEMS_
  5            from (select '1' XH, 'A1;A2;A3;' ITEMS from dual)
  6          connect by instr(ITEMS, ';', 1, rownum) > 0);XH        SEQ ITEM
-- ---------- ------------------------------------
1           1 A1
1           2 A2
1           3 A3SQL> 只有1条纪录的，我搞定了，请大牛帮忙看看怎么改成多条纪录的拆分

Answer 3

ITEMS的内容不确定，可以是   A;BBB;汉;XH ITEMS
-- ---------
1  A;BBB;汉;结果是XH ITEM
-- ----
1  A
1  BBB
1  汉这样拆分已经搞定了，请帮忙看看怎么拆多条纪录的！

Answer 4

把前面的代码优化以下，这样带空字符串的也可以拆分出来了。继续等牛牛解惑SQL> 
SQL> select XH,
  2         row_number() over(partition by XH order by ITEMS_) SEQ,
  3         substr(ITEMS_,
  4                instr(ITEMS_, ';', -1) + 1,
  5                length(ITEMS_) - instr(ITEMS_, ';', -1)) ITEM
  6    from (select XH, substr(ITEMS, 1, instr(ITEMS, ';', 1, rownum) - 1) ITEMS_
  7            from (select '1' XH, 'A;BBB;;汉;' ITEMS from dual)
  8          connect by instr(ITEMS, ';', 1, rownum) > 0);XH        SEQ ITEM
-- ---------- ------------------------------------
1           1 A
1           2 BBB
1           3 
1           4 汉SQL> 

Answer 5

SQL> select * from tt1;
 
XH ITEMS
-- ----------
1  A;BBB;;汉;
2  B1B;B2B;SQL> select xh, row_number() over(partition by XH order by lev desc) SEQ, item
  2    from (select xh, rownum lev, ITEM
  3            from (SELECT distinct xh,
  4                                  substr(ITEMS,
  5                                         instr(ITEMS, ';', -1, LEVEL + 1) + 1,
  6                                         instr(ITEMS, ';', -1, LEVEL) -
  7                                         instr(ITEMS, ';', -1, LEVEL + 1) -
  8                                         length(';')) ITEM
  9                    FROM tt1
 10                  CONNECT BY LEVEL <=
 11                             (length(ITEMS) - length(replace(ITEMS, ';'))))
 12           order by lev desc);
 
XH        SEQ ITEM
-- ---------- --------------------
1           1 A
1           2 BBB
1           3 
1           4 汉
2           1 B2B
2           2 B1B
 
6 rows selected有个问题就是B1B与B2B的顺序颠倒了