Oracle, could you tell me how to through the loop to remove duplicate data?-CodePudding

Everybody is good, if in a table, there is 1; 1; 2; 2; 3; These data, could you tell me how to cycle through after the removal of duplicate data get 1; 2; 3; The results of the four?

This result is obtained by SP (stored procedure) best,

CodePudding user response:

Please give test data, and your expected results;

CodePudding user response:

Table A:
1; 1; 2; 2; 3; 4

Table B:
1; 2; 3; 4

How do I get the data of A table by cycle to get data from table B after heavy?

CodePudding user response:

reference 1st floor wmxcn2000 response:

please give test data, and your expected results;

Table A:
1; 1; 2; 2; 3; 4

Table B:
1; 2; 3; 4

How do I get the data of A table by cycle to get data from table B after heavy?

CodePudding user response:

reference qq_34006376 reply: 3/f

Quote: refer to 1st floor wmxcn2000 response:

Please give test data, and your expected results;

Table A:
1; 1; 2; 2; 3; 4

Table B:
1; 2; 3; 4

How do I get the data of A table by cycle to get data from table B after heavy?

Why must use cycle? Efficiency is also not necessarily good
With DISTINCT well done a statement
You can also use function ROW_NUMBER

CodePudding user response:

SQL> With tt as (
2 select 1 as id from dual union all
3 select 1 as id from dual union all
4 select 2 as id from dual union all
5 the select 2 as id from dual union all
6 select 3 as id from dual union all
7 the select 3 as id from dual union all
8 the select 3 as id from dual)
9 the from the select t.i d
10 (select id, row_number () over (partition by id order by 1) rn from tt) t
11 where t.r n=1;

ID
-- -- -- -- -- -- -- -- -- --
1
2
3

CodePudding user response:

reference 5 floor sych888 reply:

SQL> With tt as (
2 select 1 as id from dual union all
3 select 1 as id from dual union all
4 select 2 as id from dual union all
5 the select 2 as id from dual union all
6 select 3 as id from dual union all
7 the select 3 as id from dual union all
8 the select 3 as id from dual)
9 the from the select t.i d
10 (select id, row_number () over (partition by id order by 1) rn from tt) t
11 where t.r n=1;

ID
-- -- -- -- -- -- -- -- -- --
1
2
3

Thank you, I just want to use the loop to realize the function, use PLSQL implementation, it is best to use SP to complete this operation

CodePudding user response:

The create or replace function ZH_SPLIT (v_string in varchar2, - the input string
V_delimiter varchar2) - input in delimiters

/*
Remove duplicate data in a string, for example: a, a, a, b, c, c outputs a, b, c
*/
Return varchar2 is
Int j:=0;
Int: I=1;
Len_string int:=0;
Len_delimiter int:=0;
STR varchar2 (4000);
V_return varchar2 (4000);
The begin
Len_string:=LENGTH (v_string);
Len_delimiter:=LENGTH (v_delimiter);
While j & lt; Len_string loop
J:=INSTR (v_string v_delimiter, I);
If j=0 then
J:=len_string;
STR:=SUBSTR (v_string, I);
If instr (v_return, STR) & gt; 0 then
Null;
The else
V_return:=v_return | | STR | | ', ';
End the if;
If I & gt;=len_string then
exit;
End the if;
The else
STR:=SUBSTR (v_string, I, j, I);
I:=j + len_delimiter;
If instr (v_return, STR) & gt; 0 then
Null;
The else
V_return:=v_return | | STR | | ', ';
End the if;
End the if;
end loop;
V_return:=substr (v_return, 1, length (v_return) - 1);
Return v_return;
end;
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
The select ZH_SPLIT (' a, b, b, c, c, d ', ', ') from dual
A, b, c, d