Tb1:
Tb2:
All names in the query table 1 in table 2, the number of occurrences of

CodePudding user response:

 select tb1. The name, 
Count (case when instr (tb2. Name, tb1. The name) & gt; 1 then 1 else 0 end) 
The from tb1, tb2 
Group by tb1. Name; 

CodePudding user response:

 select tb1. The name, 
Count (case when instr (tb2. Name, tb1. The name) & gt; Then one else 0 0 end) 
The from tb1, tb2 
Group by tb1. Name; 

Wrong, is greater than zero

CodePudding user response:

Use instr, comma in matching if table 2 the first in a li yi feng, a li yi that will also match on table 1. Instr (', li yi feng, ' ', li yi,) this is no problem

CodePudding user response:

Drop table test1.
Drop table test;
Create table test (name varchar2 (20));

Insert into test values (' a ');
Insert into test values (" b ");
Insert into test values (' d ');
Insert into test values (" c ");


The create table test1 (id number (2), manageuser varchar (40));


Insert into test1 values (1, 'a, b, c);
Insert into test1 values (2, 'a, b);
Insert into test1 values (3, 'a, b, c, d);
Insert into test1 values (4, 'a, c);


Select tb1. The name, the count (*)
From the test tb1,
(select regexp_substr (manageuser, '[^,] +' 1, level) name
The from test1 tb2
Connect by level & lt;=regexp_count (manageuser, '[and]) + 1
And the prior id=id
And (the prior dbms_random. Value) is not null) tb2
Where tb1. The name=tb2. Name
Group by tb1. Name

CodePudding user response:

Borrow upstairs data

With tt as (
The select wm_concat (t1) manageuser) mm from test1 t1)
Select name, length, t. (t2) mm) - length (the replace (t2) mm, t. name, ')) number
From the test t and tt t2

CodePudding user response:

Select the test. The name,
The sum (case when instr (test_1 manageuser, test. The name) & gt; Then one else 0 0 end)
From the test, test_1
Group by the test. The name;

CodePudding user response:

refer to the second floor js14982 response:

 select tb1. The name, 
Count (case when instr (tb2. Name, tb1. The name) & gt; Then one else 0 0 end) 
The from tb1, tb2 
Group by tb1. Name; 

Wrong, is greater than 0

field combined with single quotes around is perfect