现有如下数据
table1
id name(nvarchar2(200))
1 a1
2 a2
3 a3
1 b1
3 b3
1 c1就是把相同的id的不同行合并
结果是
id name
1 a1,b1,c1
2 a2
3 a3,b3哪位大哥帮我写个存储过程如cast(), 我想select id,cast(name) as names ...调用,但是存储过程写不出来我的是oracle9i,我不需要sql语句
table1
id name(nvarchar2(200))
1 a1
2 a2
3 a3
1 b1
3 b3
1 c1就是把相同的id的不同行合并
结果是
id name
1 a1,b1,c1
2 a2
3 a3,b3哪位大哥帮我写个存储过程如cast(), 我想select id,cast(name) as names ...调用,但是存储过程写不出来我的是oracle9i,我不需要sql语句
解决方案 »
- PL/SQL中一打逗号就死机了?
- 数据库查询语句功能
- 在sqlplus中怎样写if语句
- 请问如何对ORACLE导入数据,常用的方法举例+详细说明一下!!~~谢谢
- Oracel能够把AL32UTF8编码的繁体字转换为Big5编码么?繁体站点注册的会员发送繁体电子邮件杂志,会员姓名是乱码,这个问题怎么解决?
- 关于日期格式的一个问题
- 修改了某表结构,相关的触发器、视图、存储过程、函数等等都会打红叉,如何一批把他们都重新编译了?
- plsql developer 的导出CSV问题
- 急请大虾:请问连接ORACLE时分页程序怎么写?
- sqlserver的@@IDENTITY在oracle中怎样实现?
- jsp+oracle编程中登录信息检验问题,急用啊,谢谢!!!
- sqlldr语句用法
REPLACE(MAX(LTRIM(SYS_CONNECT_BY_PATH(DECODE(RN,
1,
TO_CHAR(NAME),
' '),
','),
',')),
' ,',
'') A
FROM (SELECT TT.*, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ROWNUM) RN
FROM TABLE1 TT)
START WITH RN = 1
CONNECT BY PRIOR RN = RN - 1
GROUP BY USER_ID
按照这个写过程
SQL> --似乎比较麻烦,要用到自定义类型,而且不能对所有的表都通用。我只能针对这个表写个自定义类型的了。
SQL> --期待高手解决。
SQL>
SQL> create or replace type t_order_table1 as table of varchar2(4000);
2 /类型已创建。SQL>
SQL> create or replace function f_order(i_name in varchar2)
2 return sys_refcursor as
3 var_t_table t_order_table1;
4 var_cur sys_refcursor;
5 var_id number;
6 var_name varchar2(200);
7 var_rn number;
8 var_current_id number;
9 v_sql varchar2(4000);
10 var_cur_ret sys_refcursor;
11 begin
12 v_sql := 'select tt.id,tt.'||i_name||',row_number() over(partition by id order by id) rn from table1 tt';
13
14 dbms_output.put_line(v_sql);
15 var_t_table := t_order_table1();
16
17 open var_cur for v_sql;
18
19 var_current_id :=-1;
20
21
22 loop
23 fetch var_cur into var_id,var_name,var_rn;
24 exit when var_cur%NOTFOUND;
25 if(var_current_id<>var_id) then
26 var_current_id := var_id;
27 var_t_table.extend();
28 var_t_table(var_t_table.count) := to_char(var_id)||'|'||var_name;
29 else
30 var_t_table(var_t_table.count) := var_t_table(var_t_table.count)||','||var_name;
31 end if;
32
33 end loop;
34 close var_cur;
35 --return var_t_table;
36 open var_cur_ret for select * from THE (select cast(var_t_table as t_order_table1) from dual);
37 return var_cur_ret;
38 exception when others then
39 raise;
40
41 end;
42 /函数已创建。SQL>
SQL> select f_order('name') from dual;F_ORDER('NAME')
--------------------
CURSOR STATEMENT : 1CURSOR STATEMENT : 1COLUMN_VALUE
---------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------1|a1,b1,c1
2|a2
3|a3,b3
SQL>
名称 是否为空? 类型
----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------- ID NUMBER(10)
NAME VARCHAR2(200)SQL> select * from table1; ID NAME
---------- --------------------
1 a1
1 b1
1 c1
2 a2
3 a3
3 b3已选择6行。SQL>
MAX(decode(rn, 1, col, NULL)) ||
MAX(decode(rn, 2, ',' || col, NULL)) ||
MAX(decode(rn, 3, ',' || col, NULL)) name
FROM (SELECT id,
name,
row_number() over(PARTITION BY id ORDER BY name) AS rn
FROM tabel1) t
GROUP BY id
ORDER BY 1;
select id,substr(max(sys_connect_by_path(name,',')),2)name
from (
select id,name,
row_number()over(partition by id order by name ) rn
from table1)
start with rn=1
connect by prior rn=rn-1 and id=prior id
group by id如果不要sql,要写函数
摘了一个,试试
create or replace type string_sum_obj as object (
--聚合函数的实质就是一个对象
sum_string varchar2(4000),
static function ODCIAggregateInitialize(v_self in out string_sum_obj) return number,
--对象初始化
member function ODCIAggregateIterate(self in out string_sum_obj, value in varchar2) return number,
--聚合函数的迭代方法(这是最重要的方法)
member function ODCIAggregateMerge(self in out string_sum_obj, v_next in string_sum_obj) return number,
--当查询语句并行运行时,才会使用该方法,可将多个并行运行的查询结果聚合
member function ODCIAggregateTerminate(self in string_sum_obj, return_value out varchar2 ,v_flags in number) return number
--终止聚集函数的处理,返回聚集函数处理的结果.
)
/
create or replace type body string_sum_obj is
static function ODCIAggregateInitialize(v_self in out string_sum_obj) return number is
begin
v_self := string_sum_obj(null);
return ODCICONST.Success;
end;
member function ODCIAggregateIterate(self in out string_sum_obj, value in varchar2) return number is
begin
/* 连接 */
if self.sum_string is null then self.sum_string:=value;
else self.sum_string := self.sum_string ||','|| value;
end if;
return ODCICONST.Success;
/* 最大值 */
if self.sum_string<value then
self.sum_string:=value;
end if;
/* 最小值 */
if self.sum_string>value then
self.sum_string:=value;
end if;
return ODCICONST.Success;
end;
member function ODCIAggregateMerge(self in out string_sum_obj, v_next in string_sum_obj) return number is
begin
/* 连接 */
self.sum_string := self.sum_string || v_next.sum_string;
return ODCICONST.Success;
/* 最大值 */
if self.sum_string<v_next.sum_string then
self.sum_string:=v_next.sum_string;
end if; /* 最小值 */
if self.sum_string>v_next.sum_string then
self.sum_string:=v_next.sum_string;
end if;
return ODCICONST.Success;
end;
member function ODCIAggregateTerminate(self in string_sum_obj, return_value out varchar2 ,v_flags in number) return number is
begin
return_value:= self.sum_string;
return ODCICONST.Success;
end;
end;
/
create or replace function ConnStrSum(value Varchar2) return Varchar2
parallel_enable aggregate using string_sum_obj;
然后调用函数
with tt as(select 1 id,'a1' name from dual
union all select 1,'a2' from dual
union all select 2,'b1' from dual
union all select 2,'b2' from dual)
select id,ConnStrSum(name)
from tt
group by id
CREATE TABLE tt AS
SELECT * FROM
(select 12345 user_id,1 pro_id,9 pro_type,10 inner_type from dual
union all select 12345,2,9,10 from dual
union all select 12345,5,8,10 from dual
union all select 12345,6,5,10 from dual
union all select 12345,7,3,10 from dual
union all select 123456,7,3,10 from dual) tt ;
select user_id,replace(max(ltrim(sys_connect_by_path(decode(rn1,1, to_char(pro_id),' '),','),',')),' ,','')a,
replace(max(ltrim(sys_connect_by_path(decode(rn2,1, to_char(pro_type),' '),','),',')),' ,','')b,
replace(max(ltrim(sys_connect_by_path(decode(rn3,1, to_char(inner_type),' '),','),',')),' ,','')c
from (select tt.*,row_number()over(partition by user_id,pro_id order by rownum)rn1,
row_number()over(partition by user_id,pro_type order by rownum)rn2,
row_number()over(partition by user_id,inner_type order by rownum)rn3,
row_number()over(partition by user_id order by rownum)rn from tt)
connect by prior rn=rn-1
group by user_id;DROP TABLE tt
insert into t2 values('001','vl1');
insert into t2 values('001','vl2');
insert into t2 values('001','vl3');
insert into t2 values('002','vl1');
insert into t2 values('002','vl2'); SELECT COL1, LTRIM(MAX(SYS_CONNECT_BY_PATH(COL2, ',')), ',') COL2
FROM (SELECT COL1,
COL2,
MIN(COL2) OVER(PARTITION BY COL1) COL2_MIN,
(ROW_NUMBER() OVER(ORDER BY COL1, COL2)) +
(DENSE_RANK() OVER(ORDER BY COL1)) NUMID
FROM T2)
START WITH COL2 = COL2_MIN
CONNECT BY NUMID - 1 = PRIOR NUMID
GROUP BY COL1 /*
COL1 COL2
---------- ----------------------------------------
001 vl1,vl2,vl3
002 vl1,vl2
2 rows selected
*/
select id,name,name_path from(
select id,rank,name_path,row_number()over(partition by id order by rank desc) id_path_rank
from(
select id,name,rank,ltrim(sys_connect_by_path(id,','),',') name_path
from(
select id ,name,row_number()over(partition by id order by name) rank
from table1 order by id) connect by id = prior id and rank-1=prior rank))
where name_path_rank=1;
你可以试试
group by id;
group by id;
创建参考:
http://hi.baidu.com/38608338/blog/item/91e3a71629daab03c83d6d31.html
狂浪已经把函数写出来了,【ConnStrSum】这个应该就是你想要的。你这是要自己写一个聚合函数[aggregate function],写聚合函数就是要这么写的
1、建一个 object 包含一个成员变量 四个方法
2、建这个 object 的body 实现这个四个方法,这四个方法就是实现聚合函数的主要功能
你想要的功能完全在这四个函数里面实现3、建一个函数 使用这个 object