参考这个:现有一表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 SQL> select * from document; LID LLABEL ---------- ------------------------------ 1 china,people,1 2 china,2,3 3 china,1SQL> SQL> select new_label, count(1) counts 2 from (select substr(',' || llabel || ',', 3 instr(',' || llabel || ',', ',', 1, rn) + 1, 4 instr(',' || llabel || ',', ',', 1, rn + 1) - 5 instr(',' || llabel || ',', ',', 1, rn) - 1) as new_label 6 from document do, 7 (select rownum rn from all_objects where rownum <= 10) ao 8 where instr(',' || llabel, ',', 1, rn) > 0) t1 9 group by new_label 10 order by 2 desc;NEW_LABEL COUNTS -------------------------------- ---------- china 3 1 2 2 1 people 1 3 1
用一个存储过程试试: 先建表 create table tabrow (id varchar2(2), name varchar2(100)); 插入数据 insert into tabrow values('a','11;12;22;33;'); insert into tabrow values('b','33;44;'); commit;目标表,结果数据要插入此表中 create table tabcol (id varchar2(2), name varchar2(10));存储过程代码如下 create or replace procedure transferToCol(p_row_id varchar2, p_row_name varchar2) as l_rowstring varchar2(1000); l_row_tab dbms_utility.uncl_array; l_tablen number; l_x_pos number; begin l_rowstring := substr(p_row_name,1,length(p_row_name)-1); --去掉最后一个';' l_rowstring := replace(l_rowstring,';',','||p_row_id||'X'); --将';'替换为','||p_row_id,如'33;44'替换为'33,aX44' l_rowstring := p_row_id || 'X' || l_rowstring; --前面补上一个'a',如'aX33,aX44' dbms_utility.comma_to_table(l_rowstring, l_tablen, l_row_tab); --将aX33,aX44转入到一个TABLE表中 for i in 1..l_tablen loop --将TABLE表中的数据插入到tabcol表中 l_x_pos := instr(l_row_tab(i), 'X'); INSERT INTO tabcol VALUES(substr(l_row_tab(i), 1, l_x_pos - 1), substr(l_row_tab(i), l_x_pos + 1)); end loop; commit; end; /开始数据分列begin for r in (select id,name from tabrow) loop transfertocol(r.id, r.name); end loop; end; / 看结果 select * from tabcol;
没遇到过,个人感觉需要如果非要在oracle端实现需要自己写pl/sql实现, 需要自定义一个recode类型如:record_row_type里面包括id,name 再自定义一个table类型如:TYPE record_table_type IS TABLE OF record_row_type INDEX BY BINARY_INTEGER; 再在过程中将字符串拆分,将结果放入recode,再将结果放入table. 最后返回table.如果用到了hibernate,可以用hibernate的自定义数据类型。
2楼仁兄多谢了!!!牛啊。 问题解决,现贴出实现本例的SQL语句(pl/sql中),供大家参考。 select ID,substr(';' || NAME || ';' ,instr(';' || NAME || ';', ';', 1, rn) + 1 ,instr(';' || NAME || ';', ';', 1, rn + 1) - instr(';' || NAME || ';', ';', 1, rn) - 1) as NAME from te,(select rownum rn from all_objects where rownum <= 10) where instr(NAME, ';', 1, rn) > 0 order by ID
SELECT Id, Substr(';' || NAME || ';', Instr(';' || NAME || ';', ';', 1, Rn) + 1, Instr(';' || NAME || ';', ';', 1, Rn + 1) - Instr(';' || NAME || ';', ';', 1, Rn) - 1) AS NAME FROM Tt, (SELECT LEVEL Rn FROM Dual CONNECT BY LEVEL < (SELECT MAX(Length(Tt.NAME)) FROM Tt)) WHERE Instr(NAME, ';', 1, Rn) > 0 ORDER BY Id
id label
1 中国,人民,1
2 中国,2,3
3 中国,1 现在需要用一句sql查询出document表中label出现的次数排序,结果要如下这样:
label num
中国 3
1 2
人民 1
2 1
3 1 SQL> select * from document; LID LLABEL
---------- ------------------------------
1 china,people,1
2 china,2,3
3 china,1SQL>
SQL> select new_label, count(1) counts
2 from (select substr(',' || llabel || ',',
3 instr(',' || llabel || ',', ',', 1, rn) + 1,
4 instr(',' || llabel || ',', ',', 1, rn + 1) -
5 instr(',' || llabel || ',', ',', 1, rn) - 1) as new_label
6 from document do,
7 (select rownum rn from all_objects where rownum <= 10) ao
8 where instr(',' || llabel, ',', 1, rn) > 0) t1
9 group by new_label
10 order by 2 desc;NEW_LABEL COUNTS
-------------------------------- ----------
china 3
1 2
2 1
people 1
3 1
先建表
create table tabrow
(id varchar2(2),
name varchar2(100));
插入数据
insert into tabrow values('a','11;12;22;33;');
insert into tabrow values('b','33;44;');
commit;目标表,结果数据要插入此表中
create table tabcol
(id varchar2(2),
name varchar2(10));存储过程代码如下
create or replace procedure transferToCol(p_row_id varchar2, p_row_name varchar2)
as
l_rowstring varchar2(1000);
l_row_tab dbms_utility.uncl_array;
l_tablen number;
l_x_pos number;
begin
l_rowstring := substr(p_row_name,1,length(p_row_name)-1); --去掉最后一个';'
l_rowstring := replace(l_rowstring,';',','||p_row_id||'X'); --将';'替换为','||p_row_id,如'33;44'替换为'33,aX44'
l_rowstring := p_row_id || 'X' || l_rowstring; --前面补上一个'a',如'aX33,aX44'
dbms_utility.comma_to_table(l_rowstring, l_tablen, l_row_tab); --将aX33,aX44转入到一个TABLE表中
for i in 1..l_tablen loop --将TABLE表中的数据插入到tabcol表中
l_x_pos := instr(l_row_tab(i), 'X');
INSERT INTO tabcol VALUES(substr(l_row_tab(i), 1, l_x_pos - 1), substr(l_row_tab(i), l_x_pos + 1));
end loop;
commit;
end;
/开始数据分列begin
for r in (select id,name from tabrow) loop
transfertocol(r.id, r.name);
end loop;
end;
/
看结果
select * from tabcol;
需要自定义一个recode类型如:record_row_type里面包括id,name
再自定义一个table类型如:TYPE record_table_type IS TABLE OF record_row_type INDEX BY BINARY_INTEGER;
再在过程中将字符串拆分,将结果放入recode,再将结果放入table.
最后返回table.如果用到了hibernate,可以用hibernate的自定义数据类型。
问题解决,现贴出实现本例的SQL语句(pl/sql中),供大家参考。
select ID,substr(';' || NAME || ';'
,instr(';' || NAME || ';', ';', 1, rn) + 1
,instr(';' || NAME || ';', ';', 1, rn + 1) - instr(';' || NAME || ';', ';', 1, rn) - 1)
as NAME
from te,(select rownum rn from all_objects where rownum <= 10)
where instr(NAME, ';', 1, rn) > 0
order by ID
Substr(';' || NAME || ';',
Instr(';' || NAME || ';', ';', 1, Rn) + 1,
Instr(';' || NAME || ';', ';', 1, Rn + 1) - Instr(';' || NAME || ';', ';', 1, Rn) - 1) AS NAME
FROM Tt,
(SELECT LEVEL Rn
FROM Dual
CONNECT BY LEVEL < (SELECT MAX(Length(Tt.NAME))
FROM Tt))
WHERE Instr(NAME, ';', 1, Rn) > 0
ORDER BY Id