Rumah pangkalan data tutorial mysql Oracle数据库9i、10g里列转行各种方法比较

Oracle数据库9i、10g里列转行各种方法比较

Jun 07, 2016 pm 05:41 PM
oracle pangkalan data oracle

把字符串1,2,3,4,5,以逗号分隔,输出为行,也就是12345随手写了一个,Oracle10G以上有表如下SQLselect*fromt;IDNAM..

把字符串 '1,2,3,4,5',以逗号分隔,输出为行,也就是

1
2
3
4
5

随手写了一个, Oracle 10G 以上
有表如下



        ID NAME
---------- --------
         1 0
         1 1
         1 5
         1 2
         1 8
         1 10
         2 9
         2 7
         2 8
         3 你好
         3 他好
         3 大家好

12 rows selected.

然后客户那反馈说数据库说9i的,报错说connect by不能使用子查询。
于是再稍做修改,在确定逗号不会超过99个的前提下,直接写个常量100,可以支持9i
 

后来想想这样也不好,于是建议建立一张特定的IOT表,保存10000个数字,一般够用了。
建立一张IOT,共10000行,同样支持9i

  • create table tnumber(ele,constraint pk_tnumber primary key(ele)) organization index as 
  • select rownum id from dual connect by rownum=10000; 
  • 然后不再需要常量100,即保证准确,又保证较好的性能

            ID NAME
    ---------- --------
             1 0
             1 1
             1 5
             1 2
             1 8
             1 10
             2 9
             2 7
             2 8
             3 你好
             3 他好
             3 大家好

    12 rows selected.

    下面测试一下性能:转换50万行试试,美国服务器,测试环境:DELL D630 用了4年半的旧笔记本,磁盘都是碎片
    SQL> insert into t select rownum+4,'1,2,3,4' from dual connect by rownum

    500000 rows created.

    SQL> commit;

    Commit complete.

    set timi on
    SQL> set timi on
    SQL> create table t500 nologging as
         with   vmaxnum as (
                select  ele
                from    tnumber
                where   ele      select id,
                decode(pos,0,substr(name,lagpos+1),substr(name,lagpos+1,pos-lagpos-1)) name
         from   (select id,name,ele,pos,nvl(lag(pos) over (partition by id order by ele),0) lagpos
                 from   (select id,name,ele,
                                instr(name,',',1,ele) pos
                         from   (select /*+ all_rows no_merge(v2) use_merge(t,v2) */
                                        t.id,t.name,v2.ele
                                 from   t,vmaxnum v2
                                 where  ele      order  by id,ele;

    Table created.

    Elapsed: 00:00:16.48

    耗时16秒,香港服务器,含建表的写盘时间

    SQL> select count(*) from t500;

      COUNT(*)
    ----------
       2000012

    当然还有别的写法,比如简单的可以这样

    或者10G以上用正则表达式也可以实现
    这个方法可能不是最高效的,但也还可以。
    这个SQL的性能和字符串含有的逗号的个数有关,逗号越多,也就是分隔项越多,性能越差。

    本文出自 “专注J2EE系列规范下的..” 博客,请务必保留此出处

    ,美国空间
    Kenyataan Laman Web ini
    Kandungan artikel ini disumbangkan secara sukarela oleh netizen, dan hak cipta adalah milik pengarang asal. Laman web ini tidak memikul tanggungjawab undang-undang yang sepadan. Jika anda menemui sebarang kandungan yang disyaki plagiarisme atau pelanggaran, sila hubungi admin@php.cn

    Tag artikel panas

    Notepad++7.3.1

    Notepad++7.3.1

    Editor kod yang mudah digunakan dan percuma

    SublimeText3 versi Cina

    SublimeText3 versi Cina

    Versi Cina, sangat mudah digunakan

    Hantar Studio 13.0.1

    Hantar Studio 13.0.1

    Persekitaran pembangunan bersepadu PHP yang berkuasa

    Dreamweaver CS6

    Dreamweaver CS6

    Alat pembangunan web visual

    SublimeText3 versi Mac

    SublimeText3 versi Mac

    Perisian penyuntingan kod peringkat Tuhan (SublimeText3)

    Berfungsi untuk mengira bilangan hari antara dua tarikh dalam oracle Berfungsi untuk mengira bilangan hari antara dua tarikh dalam oracle May 08, 2024 pm 07:45 PM

    Berfungsi untuk mengira bilangan hari antara dua tarikh dalam oracle

    Berapa lama log pangkalan data Oracle akan disimpan? Berapa lama log pangkalan data Oracle akan disimpan? May 10, 2024 am 03:27 AM

    Berapa lama log pangkalan data Oracle akan disimpan?

    Urutan langkah permulaan pangkalan data oracle ialah Urutan langkah permulaan pangkalan data oracle ialah May 10, 2024 am 01:48 AM

    Urutan langkah permulaan pangkalan data oracle ialah

    Cara menggunakan selang dalam oracle Cara menggunakan selang dalam oracle May 08, 2024 pm 07:54 PM

    Cara menggunakan selang dalam oracle

    Keperluan konfigurasi perkakasan pelayan pangkalan data Oracle Keperluan konfigurasi perkakasan pelayan pangkalan data Oracle May 10, 2024 am 04:00 AM

    Keperluan konfigurasi perkakasan pelayan pangkalan data Oracle

    Bagaimana untuk menentukan sama ada dua rentetan terkandung dalam oracle Bagaimana untuk menentukan sama ada dua rentetan terkandung dalam oracle May 08, 2024 pm 07:00 PM

    Bagaimana untuk menentukan sama ada dua rentetan terkandung dalam oracle

    Bagaimana untuk melihat bilangan kemunculan watak tertentu dalam Oracle Bagaimana untuk melihat bilangan kemunculan watak tertentu dalam Oracle May 09, 2024 pm 09:33 PM

    Bagaimana untuk melihat bilangan kemunculan watak tertentu dalam Oracle

    Berapa banyak memori yang diperlukan oleh oracle? Berapa banyak memori yang diperlukan oleh oracle? May 10, 2024 am 04:12 AM

    Berapa banyak memori yang diperlukan oleh oracle?

    See all articles