[size=large][b]splitstr函数[/b][/size]
[size=large][b]使用splitstr函数[/b][/size]
[color=red]假如表A的一列数据有A1,A2等分隔符分开 此时需要对表A的这个字段进行统计[/color]
CREATE OR REPLACE FUNCTION splitstr(p_string IN VARCHAR2, p_delimiter IN VARCHAR2)
RETURN str_split PIPELINED
AS
v_length NUMBER := LENGTH(p_string);
v_start NUMBER := 1;
v_index NUMBER;
BEGIN
WHILE(v_start <= v_length)
LOOP
v_index := INSTR(p_string, p_delimiter, v_start);
IF v_index = 0
THEN
PIPE ROW(SUBSTR(p_string, v_start));
v_start := v_length + 1;
ELSE
PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
v_start := v_index + 1;
END IF;
END LOOP;
RETURN;
END splitstr;
[size=large][b]使用splitstr函数[/b][/size]
SELECT COLUMN_VALUE FROM TABLE(SPLITSTR('1,9', ',')) ;
[color=red]假如表A的一列数据有A1,A2等分隔符分开 此时需要对表A的这个字段进行统计[/color]
--id中的值可能有1,2 1 2 等
SELECT
R.NAME ,
SUM(DECODE(E.STATUS,1,1,0)) AS 状态为1的总数,
SUM(DECODE(E.STATUS,2,1,0)) AS 状态为2的总数
FROM A E, 字典表 R
WHERE EXISTS (SELECT COLUMN_VALUE
FROM TABLE(SPLITSTR(E.id, ',')) it
WHERE it.column_value = r.id)
GROUP BY R.NAME;