For example:
A table
Field (primary key) 2
1 the ABC; Def
2, 123; 456
Table B
Field (primary key) 2
3 the abcd
4 456
Determine if A table field with A semicolon 2 values in table B, there are not record, and record the data.
Example returns A table data
1 the ABC
1 def
2 123
Consult the SQL how to write

CodePudding user response:

Determine if A table field with A semicolon 2 values in table B, there are not record, and record the data.
Example returns A table data
1 the ABC
1 def
2 123

Why didn't see this logic will return this data?

CodePudding user response:

1 def


This data, it is according to the rule contains?

CodePudding user response:

The select a.c ol1, biggest ol2
From a, b
Where instr (a.c ol2, biggest ol2) & gt; 0;

More a little bit more simple is instr, is on the case, but there are defects, such as table fields is ab, b instead of ABC will match to
Complex is the field in a table for 2 whole column, and then an exact match table field 2 b

CodePudding user response:

reference 1st floor xiaoguanzhao response:

judge if A table field with A semicolon 2 values in table B, there are not record, and record the data.
Example returns A table data
1 the ABC
1 def
2 123

Why didn't see this logic will return this data?

Is unwrapped fields in the table 2 has A semicolon after the value of the judgment whether there is in the table field 2 B

CodePudding user response:

refer to the second floor wmxcn2000 response:

1 def


This data, it is according to the rule contains?

refer to the second floor wmxcn2000 response:

1 def


This data, it is according to the rule contains?

Put A table 2 the value of the field to the existence of A semicolon then in judging table B and there is no record

CodePudding user response:

Consider this and help you;

http://bbs.csdn.net/topics/392043373

CodePudding user response:

Break up first, then match,
Will a table 1 ABC; Def forming such as
1 the ABC
1 def
To link table b

Up from efficiency will actually, use stored procedures to handle may be more appropriate

CodePudding user response:

The select aaaa. Id, aaaa. STR from (
Select distinct aaa. Id, aaa. STR, aaa. The flag, the count (*) count
The from (select aa. Id,
Aa. STR STR,
B.s tr str1,
Case
The when aa. STR=b.s tr then
1
The else
0
End flag
The from (select Anderson d, to_char (wm_concat (a.s tr) STR
The from (select t.i d,
Case
The when instr (t.s tr, '; ') & gt; 0 then
Substr (t.s tr, 1, instr (t.s tr, '; ') - 1)
End STR - if there is a semicolon, cutting the semicolon first half field
Case
The when instr (t.s tr, '; ') & gt; 0 then
Substr (t.s tr,
Instr (t.s tr, '; ') + 1,
Length (t.s tr) - instr (t.s tr, '; ')) - cutting field
End str1 - if a semicolon, cutting the semicolon after half of the field
The from (select 1 ID, 'ABC; Def 'STR
The from dual
Union all
Select 2, '123; 456 'the from dual) t) a
Group by Anderson, d - turn in the first half is a line list
The union
The select Anderson d, to_char (wm_concat (a.s tr1)) STR
The from (select t.i d,
Case
The when instr (t.s tr, '; ') & gt; 0 then
Substr (t.s tr, 1, instr (t.s tr, '; ') - 1)
End STR - if there is a semicolon, cutting the semicolon first half field
Case
The when instr (t.s tr, '; ') & gt; 0 then
Substr (t.s tr,
Instr (t.s tr, '; ') + 1,
Length (t.s tr) - instr (t.s tr, '; ')) - cutting field
End str1 - if a semicolon, cutting the semicolon after half of the field
The from (select 1 ID, 'ABC; Def 'STR
The from dual
Union all
Select 2, '123; 456 'the from dual - test data
) t) a
Group by Anderson, d - turn half part of the line after the semicolon column

Aa)

Left the join

(select three ID, 'abcd' STR
The from dual
Union all
Select 4, '456' the from dual - test data
A cartesian product) b=1 on 1 - such as cutting after field is the same as the b in the table are marked as 1, different marks of 0

) aaa
Group by aaa. Id, aaa. STR, aaa. The flag
) aaaa where aaaa. The flag=0 and aaaa. Count=(select count (*) from (select 3 ID, 'abcd' STR
The from dual
Union all
Select 4, '456' the from dual))
- the flga markers of 0 and does not exist in table B records list


Seems to write a bit more complicated, for reference only