sql语句:横表到纵表的方法

sql语句探讨:如何将一条记录按条件拆分成多条记录

SQL语句非常的灵活,在日常应用中我们经常遇到表中数据结构的转换,比如纵表到横表的转换,通常我们用decode 加 group by 来实现,这个我记得有同事已经发表过这样的文章。今天我们要讨论的是如何把横的表转换成纵表数据。当然如果使用函数或者过程,这样的问题是简单的,下面我们要讨论的是试图通过SQL语句的方式来解决这个问题。

以下仅仅为我个人对此问题的解决方法,方法应该很多,欢迎大家讨论.

先看一下wzcyd网友提问的问题

问 题:

现有一表document,有一字段label用于标识标签的字段,label之间用“,”分隔,表中现有的记录如下:

id   label

1   中国,人民,1

2   中国,2,3

3   中国,1

现在需要用一句sql查询出document表中label出现的次数排序,结果要如下这样:

label    num

中国  3

1    2

人民   1

2      1

3      1

这个问题应该是非常典型的横表变成总表的例子了。

横表到纵表,横表只有1条记录,简单select from语句无论如何修改,记录条数总是不会变的,所以首先需要解决的一个问题是1行记录如何到n行记录的转变。

用一个>n条记录的大表来产生n条记录这是一个方法。例如网友mantisXF提供的一个语句:

select new_label, count(1) counts

  from (select substr(',' || llabel || ',',

                      instr(',' || llabel || ',', ',', 1, rn) + 1,

                      instr(',' || llabel || ',', ',', 1, rn + 1) -

                      instr(',' || llabel || ',', ',', 1, rn) - 1) as new_label

          from document do,

               (select rownum rn from all_objects where rownum <= 10) ao

         where instr(',' || llabel, ',', 1, rn) > 0) t1

 group by new_label

 order by 2 desc;

为解决逗号的个数问题,略做改进:

    select new_label, count(1) counts

  from (select substr(',' || llabel || ',',

                      instr(',' || llabel || ',', ',', 1, rn) + 1,

                      instr(',' || llabel || ',', ',', 1, rn + 1) -

                      instr(',' || llabel || ',', ',', 1, rn) - 1) as new_label

          from document do,

               (select rownum rn

                  from all_objects

                 where rownum <= (select max(length(llabel) -

                                             length(replace(llabel, ',')) + 1)

                                    from document)) ao

         where instr(',' || llabel, ',', 1, rn) > 0) t1

 group by new_label;

 

这是一个解决的方法,但是不能保证你的表记录数可以大于n.另外使用数据以外的表就代表语句不可以通用。

所以我们需要选择一个新的方法来生成足够多的行数,用自身表 + connect by 递归的方式来生成足够多的记录是一个不错的选择,当然他的数据结构本身并不是树形结构,所以无法使用任何的接点关系。

按照上面的思路就可以解决问题了。

--建表

create table tmp_hzf_test1

(id number ,

label varchar2(100)) ;

--数值

1  中国,人民,1

2  中国,2,3

3  中国,1

--语句

select element_name, count(*)

  from (select distinct id,

                        level,

                        substrb(label,

                                decode(level,

                                       1,

                                       1,

                                       instrb(label, ',', 1, level - 1) + 1),

                                decode(instrb(label, ',', 1, level),

                                       0,

                                       lengthb(label) + 1,

                                       instrb(label, ',', 1, level)) -

                                decode(level,

                                       1,

                                       1,

                                       instrb(label, ',', 1, level - 1) + 1)) element_name

          from tmp_hzf_test1

        connect by level <=

                   Lengthb(label) - lengthb(replace(label, ',', '')) + 1)

 group by element_name

--结果

1   2

2   1

3   1

人民 1

中国 3

 

看起来上面的语句好像已经解决了转换问题。但是实际我们发现使用connect by 的时候由于不能使用任何的条件,他的每增加一层,都是按照指数增长的数量,效率是我们需要关注的一个问题。假设A表有m条记录,那么他使用connect by(在没有使用任何连接条件的情况下)的第n层的数量就是m的n次方。以这样惊人的速度增长,任何的数据库都是不能接受的。

网友mantisXF就对我的语句的效率提出了质疑,在上表中继续插入数据 :

4 china,4,1,china,4,1

5 china,4,1,china,4,1,china,21,3,country,29,3,china,28,3,china,30,3

6 china,21,3,china,29,3,country,4,1,china,4,1,country,21,3,china,29,3,country,21,3

7 china,43,12,china,41,1

8  people

9  people,4,1,country,4,1

10 country,4,1,good,4,1

发现上面的语句在一份钟内没有任何的反应。因为10条记录,最多的一个元素格式是21个,所以最后一层的数量已经达到了10的21次方。

所以我们还需要对上面的语句进行改造。既然每一层的数据量是m的n次方,那么只要满足m=1,每一层的数量就永远等于1,这就是我们想要的结果。按照这样的思路,我们需要把列数先做出来,使用这样的一个语句:

select level rn

  from (select max(Lengthb(label) - lengthb(replace(label, ',', '')) + 1) mrn

  from tmp_hzf_test1)

connect by level <= mrn

就可以构造出我们想要的一个每层只有一个元素的数据层次结构了。

原来的语句就可以改造为:

select element_name, count(*)

  from (select distinct id,

                        rn,

                        substrb(label,

                                decode(rn,

                                       1,

                                       1,

                                       instrb(label, ',', 1, rn - 1) + 1),

                                decode(instrb(label, ',', 1, rn),

                                       0,

                                       lengthb(label) + 1,

                                       instrb(label, ',', 1, rn)) -

                                decode(rn,

                                       1,

                                       1,

                                       instrb(label, ',', 1, rn - 1) + 1)) element_name

          from tmp_hzf_test1 a,

               (select level rn

                  from (select max(Lengthb(label) -

                                   lengthb(replace(label, ',', '')) + 1) mrn

                          from tmp_hzf_test1)

                connect by level <= mrn) b

         where Lengthb(label) - lengthb(replace(label, ',', '')) + 1 >= b.rn)

 group by element_name;

这个语句就可以实现我们想要的结果了。

 

再看一个问题:网友samuellei提的问题

有表T_1(index_1,crop_id,corp_name, start_time, end_time), 需要生成T_2(index_2,corp_id,corp_name,year_time, month_time)。 其中index_1和index2由SEQUENCE产生数据如下:

T_1
index_1,  corp_id,  corp_name, start_time,  end_time

101        1      microsoft   2007-5      2007-7

102        2         ibm       2008-1      2008-2

 

T_2
index_2,  corp_id    corp_name  year_time  month_time

8          1        microsoft   2007         5

9          1        microsoft   2007         6

10         1        microsoft   2007         7

11         2           ibm       2008         1

12         2           ibm       2008         2

 

这个问题和上面的问题非常相似,同样是一行记录我们需要按照条件拆分成多条记录,按照我们上题的解决思路,我们就可以选择这么一个方式来处理

--建表

create table T_1

(index_1 number, crop_id number, corp_name varchar2(20), start_time  date, end_time date);

--插入数据如下

101 1 microsoft 2007-5-1 2007-7-1

102 2 ibm 2008-1-1 2008-2-1

--sql语句

select   crop_id,

    corp_name,

    to_char(add_months(start_time, rn - 1),'yyyymm')

from T_1 a,

  (select level rn

from (select max(months_between(end_time, start_time) + 1) mrn

from t_1)

    connect by level <= mrn) b

where (months_between(end_time, start_time) +1) >= b.rn;

--输出的结果就是

1  microsoft   200706

1  microsoft   200707

1  microsoft   200705

2  ibm    200802

2  ibm    200801

这样就完成了横表到纵表的转换了。有了这个语句,后面插入语句就非常简单了,这里就不再描述

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值