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
这样就完成了横表到纵表的转换了。有了这个语句，后面插入语句就非常简单了，这里就不再描述
扫一扫
873
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
