首页新闻找找看学习计划

oracle合并列值问题,不许用wm_concat函数,但希望得到和wm_concat一样的结果

0
悬赏园豆:100 [已解决问题] 解决于 2017-05-25 17:04

问题:oracle数据库中,不许用wm_concat函数,合并列,希望得到结果如下:
s_type  s2
水果    葡萄,哈密瓜,香瓜,火龙果
蔬菜    西兰花,茼蒿,茄子
坚果    核桃,巴旦木

数据如下:
create table t_thz_1(
  n_id number(10),
  s_mc varchar2(100),
  s_type varchar2(10)
);

truncate table t_thz_1;
insert into t_thz_1(n_id,s_mc,s_type)
select 1 n_id,'葡萄' s_mc,'水果' s_type from dual union all
select 2 n_id,'哈密瓜' s_mc,'水果' s_type from dual union all
select 3 n_id,'西兰花' s_mc,'蔬菜' s_type from dual union all
select 4 n_id,'核桃' s_mc,'坚果' s_type from dual union all
select 5 n_id,'茼蒿' s_mc,'蔬菜' s_type from dual union all
select 6 n_id,'香瓜' s_mc,'水果' s_type from dual union all
select 7 n_id,'巴旦木' s_mc,'坚果' s_type from dual union all
select 8 n_id,'茄子' s_mc,'蔬菜' s_type from dual union all
select 10 n_id,'火龙果' s_mc,'水果' s_type from dual
;
commit;

可以用case或其他方法得到 与wm_concat 一样的结果吗?

江清风的主页 江清风 | 初学一级 | 园豆:112
提问于:2017-05-22 17:45
< >
分享
最佳答案
1

貌似高版本中有个替代函数 LISTAGG,如果是低版本的话可以参考以下方法:

第一步(创建类型):

 CREATE OR REPLACE TYPE tab_str IS TABLE OF VARCHAR2(4000); 

第二步(创建函数):

 1 CREATE OR REPLACE FUNCTION fn_to_str(
 2   p_str_tab IN tab_str,
 3   p_separator IN VARCHAR2 DEFAULT ','
 4 )
 5 RETURN VARCHAR2 IS
 6   v_ret_str VARCHAR2(4000);
 7 BEGIN
 8   FOR i IN 1..p_str_tab.COUNT LOOP
 9     v_ret_str:=v_ret_str||p_separator||p_str_tab(i);
10   END LOOP;
11   RETURN v_ret_str;
12 END fn_to_str;

第三步(利用前两步创建的类型和函数及 COLLECT 函数来查询):

SELECT t.s_type,LTRIM(fn_to_str(CAST(COLLECT(t.s_mc) AS tab_str)),',') s_names
FROM demo.t_thz_1 t GROUP BY t.s_type;

查询结果如下:

S_TYPE     S_NAMES
---------- ------------------------------
坚果       核桃,巴旦木
蔬菜       西兰花,茼蒿,茄子
水果       葡萄,哈密瓜,火龙果,香瓜
收获园豆:100
韩宗泽 | 小虾三级 |园豆:674 | 2017-05-24 14:25

嗯,这个好。原来collect是在Oracle 10g中新增加了一个聚合函数.平时用得很少.不太熟.学习了

如果值不多,也可以用case when这样写:

select s_type,substr(pt||hmg||xg||hlg||xlh||th||qz||ht||bdm,2) as s_mc from (
select s_type,
max(case when s_mc='葡萄' then ','||s_mc end) as pt,
max(case when s_mc='哈密瓜' then ','||s_mc end) as hmg,
max(case when s_mc='香瓜' then ','||s_mc end) as xg,
max(case when s_mc='火龙果' then ','||s_mc end) as hlg,
max(case when s_mc='西兰花' then ','||s_mc end) as xlh,
max(case when s_mc='茼蒿' then ','||s_mc end) as th,
max(case when s_mc='茄子' then ','||s_mc end) as qz,
max(case when s_mc='核桃' then ','||s_mc end) as ht,
max(case when s_mc='巴旦木' then ','||s_mc end) as bdm
  from t_thz_1 t
  group by s_type 

江清风 | 园豆:112 (初学一级) | 2017-05-25 16:29
其他回答(2)
0

http://bbs.csdn.net/topics/390733235 你可以看看

风行天下12 | 园豆:2354 (老鸟四级) | 2017-05-23 10:06

用java的方式明白,但用数据库的方法没有说明.恰巧我想用数据库的方法

支持(0) 反对(0) 江清风 | 园豆:112 (初学一级) | 2017-05-24 10:07

@江清风: 为什么一定要在数据库成面执行呢,数据库往往都是性能瓶颈所在,太复杂的语句不建议在数据库上来处理

支持(0) 反对(0) 风行天下12 | 园豆:2354 (老鸟四级) | 2017-05-24 10:17

@YQZC: 因为俺不是做java开发滴

支持(0) 反对(0) 江清风 | 园豆:112 (初学一级) | 2017-05-24 11:24
0

可以自定义聚合函数

刘宏玺 | 园豆:14004 (专家六级) | 2017-05-23 10:10

重写wm_concat函数吗?

支持(0) 反对(0) 江清风 | 园豆:112 (初学一级) | 2017-05-24 10:08

@江清风: 自定义的意思就是想怎么写就怎么写

支持(0) 反对(0) 刘宏玺 | 园豆:14004 (专家六级) | 2017-05-25 09:09
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册