估计是找团伙算法：
DECLARE
TYPE t_str IS TABLE OF VARCHAR2(1000) INDEX BY tmp_replacement.child%TYPE;
parents t_str;
TYPE t_num IS TABLE OF NUMBER INDEX BY tmp_replacement.child%TYPE;
ranks t_num;
TYPE t_result IS TABLE OF tmp_replacement%ROWTYPE INDEX BY PLS_INTEGER;
v_result t_result;
parentX tmp_replacement.node%TYPE;
parentY tmp_replacement.node%TYPE;
rankX int;
rankY int;
last_child tmp_replacement.child%TYPE;
v_output t_str;
vstr tmp_replacement.node%TYPE;
FUNCTION find_parent(p_current IN tmp_replacement.node%TYPE)
RETURN tmp_replacement.node%TYPE
AS
BEGIN
IF parents(p_current)p_current THEN
parents(p_current) :=find_parent(parents(p_current));
END IF;
RETURN parents(p_current);
END find_parent;
BEGIN
SELECT node, child BULK COLLECT INTO v_result
FROM (SELECT LEAST(node,child) node,GREATEST(node, child) child FROM tmp_replacement
UNION SELECT LEAST(node,child),LEAST(node,child) FROM tmp_replacement
);
FOR i IN 1..v_result.COUNT LOOP
parents(v_result(i).node) := v_result(i).node;
ranks(v_result(i).node) := 0;
END LOOP;
last_child :='*';
FOR cur IN (select LEAST(node, child) AS node, GREATEST(node, child) AS child from tmp_replacement
UNION SELECT LEAST(node,child),LEAST(node,child) FROM tmp_replacement
order by 2,1)
LOOP
if cur.childlast_child then
-- find parent for x
parentX := find_parent(cur.node);
rankX := ranks(parentX);
last_child := cur.child;
ELSE
-- find parent for y
parentY := find_parent(cur.node);
rankY := ranks(parentY);
--- union x and y
CASE
WHEN parentX = parentY THEN
continue;
WHEN rankXrankY THEN
parents(parentY) := parentX;
WHEN rankX = rankY THEN
parents(parentY) := parentX;
ranks(parentX) := ranks(parentX)+1;
END CASE;
END IF;
END LOOP;
FOR i IN 1..v_result.COUNT LOOP
v_result(i).node := find_parent(v_result(i).node);
IF v_output.EXISTS(v_result(i).node) THEN
v_output(v_result(i).node):=v_output(v_result(i).node)||','||v_result(i).child;
ELSE
v_output(v_result(i).node):=v_result(i).child;
END IF;
END LOOP;
vstr := v_output.first;
while vstr is not null loop
DBMS_OUTPUT.PUT_LINE(v_output(vstr));
vstr := v_output.next(vstr);
end loop;
END;
/
01rer435e,10r435ed,435435ed,1030435df,10r435ed
10301871,10301875,10301877,10301879,10301883,10301905,10301875,10405557
86665454,87434
PL/SQL procedure successfully completed.
|
