回覆網友問題,關於一個數值和字串一起累加的問題！[@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 :

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

相關文章