回覆網友問題,關於一個數值和字串一起累加的問題！

cosio發表於2008-10-13
字串
回覆網友問題,關於一個數值和字串一起累加的問題！[@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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章