回复网友问题,关于一个数值和字符串一起累加的问题！

回复网友问题,关于一个数值和字符串一起累加的问题！[@more@]

急急急！！！请各位大哥赐教一个数值和字符串一起累加的问题！

关键字： 字符串相加

一。创建测试表
CREATE TABLE addtest(
CODE VARCHAR2(20),
MONEY NUMBER,
FLAG VARCHAR2(20)
);
INSERT INTO addtest VALUES (1001, 99.00, '新建');
INSERT INTO addtest VALUES (1002, 100.00, '编辑');
INSERT INTO addtest VALUES (1002, 300.00, '新建');
INSERT INTO addtest VALUES (1002, 500.00, '取消');
INSERT INTO addtest VALUES (1002, 700.00, '编辑');
INSERT INTO addtest VALUES (1003, 20, '取消');
INSERT INTO addtest VALUES (1003, 60, '新建');
COMMIT;
select * from addtest;

二。想要得到的结果：
CODE MONEY FLAG
1001 99 新建
1002 1600 新建,编辑(2),取消
1003 80 新建,取消

拜托各位前辈大哥不吝赐教，先谢过了，小弟急啊！

oracle 10G:

SQL

select a.code,fl,money from
2 (select code,substr(sys_connect_by_path(falg,','),2) fl,money,level le from
3 (select row_number() over(order by code) rn,code,
4 decode(count(flag),1,flag,flag||'('||count(flag)||')') falg
5 ,sum(sum(money)) over(partition by code) money from addtest group by code,flag)
6 connect by prior code=code and prior rn+1=rn) a
7 ,(select code,count(distinct flag) co from addtest group by code) b
8 where a.le=b.co and a.code=b.code;

i did :

SELECT code,sm,LTRIM(MAX(SYS_CONNECT_BY_PATH(nflag,',')),',') nflag
from
(
select code,sm,nflag,MIN(nflag) OVER(PARTITION BY code) nflag_MIN,
(ROW_NUMBER() OVER(ORDER BY code,sm,nflag))+(DENSE_RANK() OVER (ORDER BY code,sm)) NUMID
from
(
select code,sm,decode(ct, 2, flag || '(' || ct || ')', flag) nflag
from (select unique code,
flag,
count(flag) over(partition by code, flag) ct,
sum(money) over(partition by code) sm
from addtest) a
)b
)c
START WITH nflag=nflag_MIN CONNECT BY NUMID-1=PRIOR NUMID
GROUP BY code,sm;

> select * from addtest ;




CODE MONEY FLAG

-------------------- ---------- --------------------

1001 99 新建


1002 100 编辑


1002 300 新建


1002 500 取消


1002 700 编辑


1003 20 取消


1003 60 新建





7 rows selected





SQL >

SQL > select code , sm , WMSYS . WM_CONCAT ( nflag ) flag


2 from ( select a .*, decode ( ct , 2 , flag || '(' || ct || ')' , flag ) nflag


3 from ( select unique code ,


4 flag ,


5 count ( flag ) over ( partition by code , flag ) ct ,


6 sum ( money ) over ( partition by code ) sm


7 from addtest ) a ) b


8 group by code , sm


9 /




CODE SM FLAG

-------------------- ---------- --------------------------------------------------------------------------------

1001 99 新建


1002 1600 新建 , 编辑 ( 2 ), 取消


1003 80 新建 , 取消





SQL >


oracle 9i :

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/101162/viewspace-1011826/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/101162/viewspace-1011826/