SQL高手帮忙，由多行生成字符串
有表A：
orderNo frameId qty
001 1-001 10
001 1-002 10
001 1-001 10
: : : --可能同一个orderNo有多个frameId
002 1-101 10
: : :
我要得到:
orderNo remark
001 [1-001:20],[1-002:10]...
002 [1-101:10]...
我想用function生成remark的内容，但不熟Oracle，无从下手。
请大家支招！
[解决办法]
参考一下这个：
oracle中的group by实现字符型字段求和[ZT from Dev-club，原创:Xiaoyun]
-------------------------------------------------
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
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
/*
作者:xzh2000@hotmail.com
博客: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 )|| '] ' remark 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 remark
001[1-001:20],[1-002:10]
002[1-001:10]