How to query a field according to a certain character to group-CodePudding

Such as: such as query test t content of this field in the table is
T
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
A, B, C, D
How can the results of a query for
T
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
A
B
C
D

CodePudding user response:

 
- support multiple lines 

SQL> 
SQL> Create table test (int id, name varchar (50)); 
The Table created 
SQL> The begin 
2 insert into the test values (1, 'A, B, C, D, E, F'); 
3 the insert into the test values (2, '111333555,'); 
4 the end; 
5/
PL/SQL procedure successfully completed 
SQL> A10 col id format; 
SQL> Select id, regexp_substr (name, '[^,] +', 1, level) colA 
2 the from test 
3 connect by level & lt;=regexp_count (name, ', ') + 1 
4 and the prior rowid=rowid 
5 and the prior dbms_random. The value is not null 
6 the order by id, cola; 
ID COLA 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
1 A 
1 B 
1 C 
1 D 
1 E 
1 F 
2 111 
2 22 
2 333 
2 555 
10 rows selected 
SQL> Drop table test purge; 
Table dropped 

SQL>

CodePudding user response:

If without a primary key id to separate queries to group name value also can be, but the efficiency is very slow,

CodePudding user response:

refer to the second floor u012927214 response:

if without a primary key id to query to group name value also can be alone, but the efficiency is very slow,

Don't take the primary key it doesn't matter, the efficiency is the same,

CodePudding user response:

Instant without a primary key execution efficiency is very slow, I this table itself has more than 10 data, to be a view, and so after a query will need a primary key table and other associated to multiple queries, more can't run directly.

CodePudding user response:

This belongs to the database design does not reach the designated position;

Said your requirements in detail;

CodePudding user response:

Have A worksheet with A field name is A job A few people do gid (primary key), and A work table B (qid primary key, foreign key gid), A and B is A one-to-many relationship, now you need to query out what everyone do the work, everyone query A table and table B GZM (work), GZXQ details (work),

CodePudding user response:

If still in time, it is suggested that the corresponding relation, single building a table;

CodePudding user response:

 CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2) 
RETURN ty_str_split 
IS 
INT j:=0; 
INT: I=1; 
Len INT:=0; 
Len1 INT:=0; 
STR VARCHAR2 (400); 
The str_split ty_str_split: ty_str_split=(); 
The BEGIN 
Len:=LENGTH (p_str); 
Len1:=LENGTH (p_delimiter); 

WHILE j & lt; Len 
LOOP 
J:=INSTR (p_str p_delimiter, I); 

IF j=0 
THEN 
J:=len; 
STR:=SUBSTR (p_str, I); 
Str_split. The EXTEND; 
Str_split (str_split. COUNT) :=STR; 

IF I & gt; Len=
THEN 
The EXIT; 
END the IF; 
The ELSE 
STR:=SUBSTR (p_str, I, j - I); 
I:=j + len1; 
Str_split. The EXTEND; 
Str_split (str_split. COUNT) :=STR; 
END the IF; 
END LOOP; 

RETURN the str_split; 
END fn_split;

Create a split function, around after use;
Split is inefficient, can break up the data into a temporary table; Then what other correlation;