求一字符串处理函数

[编译失败]

表A:

ID1 RATE1 ID2 RATE2 ID3 RATE3 ID4 RATE4 ID5 RATE5

总共5对元素，ID代表一种组成成分，RATE代表所含比例（合计100），也可能有的只有2对、3对，剩下的空着，输出的时候将各种成分拼接成字符串 “A 50% / B 30% / C 20%”的格式，并且按成分含量由大到小的顺序排列，含量相同时按成分进行排序，现在数据都有了，也严格符合逻辑，需求是：当5个成分里其中有一个是X的时候，将X变成Y输出，如果原本就包含了Y，那就把X的成分加到Y上面进行输出。当然最好还是符合含量高的放前面的逻辑，必须用函数解决，求一思路方法。

[小德]

這樣應該滿足你的需求了，看到你題目要用函數，下面是SQL做的的，樓主可自行轉換成函數即可。

CREATE TABLE Element(
ID1 VARCHAR2(10),RATE1 NUMBER(20),
ID2 VARCHAR2(10),RATE2 NUMBER(20),
ID3 VARCHAR2(10),RATE3 NUMBER(20),
ID4 VARCHAR2(10),RATE4 NUMBER(20),
ID5 VARCHAR2(10),RATE5 NUMBER(20));
INSERT INTO Element VALUES ('A',50,'B',30,'C',20,NULL,NULL,NULL,NULL);
INSERT INTO Element VALUES ('A',60,'D',40,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Element VALUES ('E',40,'X',30,'Y',30,NULL,NULL,NULL,NULL);
INSERT INTO Element VALUES ('A',30,'B',25,'Y',25,'X',15,'M',5);
INSERT INTO Element VALUES ('A',100,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Element VALUES ('X',100,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO Element VALUES ('B',60,'Y',20,'F',15,'X',5,NULL,NULL);
INSERT INTO Element VALUES ('G',80,'X',20,NULL,NULL,NULL,NULL,NULL,NULL);
COMMIT;

WITH temp AS(
SELECT ROWNUM rn,t.* FROM Element t
)
SELECT REPLACE(wm_concat(result),',','/') FROM (
  SELECT rn,id,Sum(rate)rate,id||' '||Sum(rate)||'%'result FROM 
(
  SELECT rn,Decode(id1,'X','Y',id1) id,rate1 rate FROM temp UNION ALL 
  SELECT rn,Decode(id2,'X','Y',id2) id,rate2 rate FROM temp UNION ALL 
  SELECT rn,Decode(id3,'X','Y',id3) id,rate3 rate FROM temp UNION ALL 
  SELECT rn,Decode(id4,'X','Y',id4) id,rate4 rate FROM temp UNION ALL 
  SELECT rn,Decode(id5,'X','Y',id5) id,rate5 rate FROM temp
)WHERE id IS NOT NULL 
GROUP BY rn,id
ORDER BY rn,rate desc
)GROUP BY rn

[编译失败]

两个楼上实现了相加，不过排序还是有点问题，楼上的结果第4行会乱。

另外有点误会，表里已经有一个字段记录了原始的输出字符串比如："A 30% / B 25% / Y 25% / X 15% / M 5%"
，现在主要只是需要找到X加到Y中，排序并输出，有没有可能用正则表达式直接处理原始的字符串？

[小德]

[Quote=引用 6 楼 的回复:]

两个楼上实现了相加，不过排序还是有点问题，楼上的结果第4行会乱。

另外有点误会，表里已经有一个字段记录了原始的输出字符串比如："A 30% / B 25% / Y 25% / X 15% / M 5%"
，现在主要只是需要找到X加到Y中，排序并输出，有没有可能用正则表达式直接处理原始的字符串？
[/Quote]
忽略了wm_concat()的无序性，现修正如下：

WITH temp AS(
SELECT ROWNUM rn,t.* FROM Element t
)
SELECT rn,max(end)END FROM 
(
  SELECT rn,wm_concat(result) over (PARTITION BY rn ORDER BY rate desc)end FROM 
  (
    SELECT rn,id,Sum(rate)rate,id||' '||Sum(rate)||'%'result FROM 
   (
      SELECT rn,Decode(id1,'X','Y',id1) id,rate1 rate FROM temp UNION ALL 
      SELECT rn,Decode(id2,'X','Y',id2) id,rate2 rate FROM temp UNION ALL 
      SELECT rn,Decode(id3,'X','Y',id3) id,rate3 rate FROM temp UNION ALL 
      SELECT rn,Decode(id4,'X','Y',id4) id,rate4 rate FROM temp UNION ALL 
      SELECT rn,Decode(id5,'X','Y',id5) id,rate5 rate FROM temp
   )WHERE id IS NOT NULL GROUP BY rn,id
  )
)GROUP BY rn;

[大明湖畔_帅锅]

上数据吧 逻辑了解了点 要看具体数据才能操作

[编译失败]

大概这个样子吧，这里的ABC可能会有很多，真正的名称存在另外一个表里

ID1	RATE1	ID2	RATE2	ID3	RATE3	ID4	RATE4	ID5	RATE5
A	50	B	30	C	20				
A	60	D	40						
E	40	X	30	Y	30				
A	30	B	25	Y	25	X	15	M	5
A	100								
X	100								
B	60	Y	20	F	15	X	5		
G	80	X	20						

[hupeng213]

试试这样子行不？
WITH t AS
(
--ID1 RATE1 ID2 RATE2 ID3 RATE3 ID4 RATE4 ID5 RATE5
SELECT 'A' AS id1,
50 AS rate1,
'B' AS id2,
30 AS rate2,
'C' AS id3,
20 AS rate3,
NULL AS id4,
NULL AS rate4,
NULL AS id5,
NULL AS rate5
FROM dual
UNION ALL
SELECT'A', 60, 'D', 40,NULL,NULL,NULL,NULL,NULL,NULL FROM dual
UNION ALL
SELECT 'E', 40, 'X', 30, 'Y', 30,NULL,NULL,NULL,NULL FROM dual
UNION ALL
SELECT 'A', 30, 'B', 25, 'Y', 25, 'X', 15, 'M', 5 FROM dual
UNION ALL
SELECT 'A', 100,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL FROM dual
UNION ALL
SELECT 'X', 100,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL FROM dual
UNION ALL
SELECT 'B', 60, 'Y', 20, 'F', 15, 'X', 5,NULL,NULL FROM dual
UNION ALL
SELECT 'G', 80, 'X', 20,NULL,NULL,NULL,NULL,NULL,NULL FROM dual
)
SELECT rn,
replace(wm_concat( id1
|| ':'
|| rate1
|| '%'
|| DECODE(id2,NULL,NULL,'split' ||id2 || ':' || rate2 || '%')
|| DECODE(id3,NULL,NULL,'split' ||id3 || ':' || rate3 || '%')
|| DECODE(id4,NULL,NULL,'split' ||id4 || ':' || rate4 || '%')
|| DECODE(id5,NULL,NULL,'split' ||id5 || ':' || rate5 || '%'))
,'split','/')
as rt
FROM
(SELECT rownum AS rn ,
id1,
rate1,
id2,
rate2,
id3,
rate3,
id4,
rate4,
id5,
rate5
FROM t
) a
GROUP BY rn


RN RT
---------------------- -----------------------------
1 A:50%/B:30%/C:20%
2 A:60%/D:40%
3 E:40%/X:30%/Y:30%
4 A:30%/B:25%/Y:25%/X:15%/M:5%
5 A:100%
6 X:100%
7 B:60%/Y:20%/F:15%/X:5%
8 G:80%/X:20%

[xpingping]

wiht new_A as(
select rowid,ID1 ID,RATE1 RATE from A union all
select rowid,ID2 ,RATE2 from A union all
select rowid,ID3 ,RATE3 from A union all
select rowid,ID4 ,RATE4 from A union all
select rowid,ID5 ,RATE5 from A)
rowid 相同的就是同一条数据
select wm_concat(ID:||RATE||'%/') from 
(select rowid,ID,sum(NVL(RATE)) RATE from
(select rowid,decode(ID,'X','Y') ID,RATE from new_A) 
group by rowid, ID)
group by rowid

缺点wm_concat()连接，它不会按照排序的来。