有表A:
orderNo frameId qty
001 1-001 10
001 1-002 10
001 1-001 10
: : : --可能同一个orderNo有多个frameId
002 1-101 10
: : :
我要得到:orderNo re
001 [1-001:20],[1-002:10]...
002 [1-101:10]...我想用function生成re的内容,但不熟Oracle,无从下手。
请大家支招!
orderNo frameId qty
001 1-001 10
001 1-002 10
001 1-001 10
: : : --可能同一个orderNo有多个frameId
002 1-101 10
: : :
我要得到:orderNo re
001 [1-001:20],[1-002:10]...
002 [1-101:10]...我想用function生成re的内容,但不熟Oracle,无从下手。
请大家支招!
解决方案 »
- to_date 转换的时候出错
- 请教Oracle 11g安装时主目录的不兼容性问题
- 求一条简单的去重语句
- 急救,在线等。。。。。oracle rac on rhel4_u4错误
- oracle sql语句怎样引用变量
- 请问怎么列出表中某个字段组合重复的记录?
- 问个oracle关联的问题,为什么inner join 和left join查询出的结果集差距那么大呢??
- 90W 记录得表count怎么这么慢?
- 求高手幫忙,BW555在不!!!
- [oracle+PL/SQL Developer]32位和64位的问题
- 求助:Oracle 中远程数据库查询,无响应,急...
- 二进制写Oracle数据库blob类型字段超级慢,Why????????
-------------------------------------------------------------------------------------
SELECT A , SUM( B ) FROM MyTab GROUP BY A ;
这是大家再熟悉不过的写法,这是根据A列统计B列的和。
有些时候,B列是字符串类型的列,就像这样
A | B
---------------
a aa
a bb
a cc
139045771 33
139045771 44
139045771 55
a dd
我想要得到这样的结果:
139045771 33,44,55
a aa,bb,cc,dd
相当于想要把字符串“加和”解决的方式如下,麻烦了一点
先编译这个函数SumString
CREATE OR REPLACE FUNCTION SumString(
I_TableName IN VARCHAR2 ,
I_GroupColName IN VARCHAR2 ,
I_ResultColName IN VARCHAR2 ,
I_GroupColValue IN VARCHAR2 ,
I_Separator IN VARCHAR2
)
RETURN VARCHAR2 IS
TYPE T_Cur IS REF CURSOR ;
C_Cur T_Cur ;
V_Sql VARCHAR2(2000) ;
V_Result VARCHAR2(2000) ;
V_Tmp VARCHAR2(200) ;
V_Cnt NUMBER := 0 ;
BEGIN
V_Result := '' ;
V_Sql := 'SELECT '|| I_ResultColName ||' FROM '|| I_TableName ||' WHERE '|| I_GroupColName || ' = '''|| I_GroupColValue || '''' ;
OPEN C_Cur FOR V_Sql ;
LOOP
FETCH C_Cur INTO V_Tmp ;
EXIT WHEN C_Cur%NOTFOUND ;
IF V_Cnt = 0 THEN
V_Result := V_Tmp ;
ELSE
V_Result := V_Result || I_Separator || V_Tmp ;
END IF ;
V_Cnt := V_Cnt + 1 ;
END LOOP ;
CLOSE C_Cur ;
RETURN V_Result ;
END SUMSTRING; 再执行以下语句测试:
CREATE TABLE TestSumString( a VARCHAR2(10) , b VARCHAR2(10) ) ;
INSERT INTO TestSumString VALUES( 'a' , 'aa' ) ;
INSERT INTO TestSumString VALUES( 'a' , 'bb' ) ;
INSERT INTO TestSumString VALUES( 'a' , 'cc' ) ;
INSERT INTO TestSumString VALUES( '139045771' , '33' ) ;
INSERT INTO TestSumString VALUES( '139045771' , '44' ) ;
INSERT INTO TestSumString VALUES( '139045771' , '55' ) ;
INSERT INTO TestSumString VALUES( 'a' , 'dd' ) ;
COMMIT ;
SELECT A , SumString( 'TestSumString', 'a' , 'b' , a , ',' ) SUM_B FROM TestSumString GROUP BY A ;运行结果如下:
A SUM_B
---------- ---------------
139045771 33,44,55
a aa,bb,dffdfd,dd
和普通的GroupBy使用方法上没什么不同
SumString函数的5各参数的意思分别是:
1. 表名;
2. 你想Group BY的字段名
3. 你想sum的那个字段名
4. Group By字段的值
5. Sum字符串时的分隔符
create table a(orderNo varchar2(100), frameId varchar2(100), qty int );
/
insert into a
select '001','1-001',10 from dual
union all
select '001','1-002',10 from dual
union all
select '001','1-001',10 from dual
union all
select '002','1-001',10 from dual;/
--create function
create or replace function sum_string(v_sql varchar2)
return varchar2
/*
作者:[email protected]
博客:http://blog.itpub.net/xzh2000
*/
as
/* 声明动态游标变量 */
type cur_alldata is ref cursor;
l_alldata cur_alldata;
/* 查询sql及其变量 */
v_row varchar2(99);
v_sum varchar2(3999);
begin
open l_alldata for v_sql;
loop
fetch l_alldata into v_row;
/* 没有数据却退出 */
exit when l_alldata%notfound;
v_sum := v_sum||'],['||v_row;
end loop; v_sum := substr(v_sum,2);
close l_alldata; return v_sum;
end;
/
--execute sql
select orderno,substr(sum2,2,length(sum2)-1 )||']' re from
(select distinct orderNo,sum_string('select frameId||'':''||sum(qty) from a where orderNo='''||orderNo||''' group by orderNo,frameId') as sum2 from a)
--result
orderno re
001 [1-001:20],[1-002:10]
002 [1-001:10]
用函数应该快一些而且简洁
>>同一个SQL写在存储过程里,会比外面直接调用快吗?
如果简单的sql语句,效果应该是一样的
>>因为发现现时的公司有些对存储过程用得有点太过了
简单的一句sql就没有必要使用存储过程了,用不用无所谓没有必要钻牛角尖