select accode from asset where accode like 'accode' AND SUBSTR(accode, length('accode'), 3) >= 1 and SUBSTR(accode, length('accode'), 3) <= 999對字段的後三位進行截取處理就可以
我的已经是B的子串了。因为还有个条件length=length+3; 如果直接判断b.accode是a.accode的子字符串,且两个不能相同 instr(a.accode, b.accode)>0 and a.accode<>b.accode;
select accode from asset where substr(accode,1,6) = 'accode' AND SUBSTR(accode, length('accode'), 3) >= 1 and SUBSTR(accode, length('accode'), 3) <= 999
--参考一下: SQL> select tname,cname from col where cname like '%ME%';TNAME CNAME ------------------------------ ------------------------------ EMPLOYEE FIRST_NAME EMPLOYEE LAST_NAME EMPLOYEE1 FIRST_NAME EMPLOYEE1 LAST_NAME IP_ADDRESS USERNAME IP_ADDRESS LOGON_TIME RECORD MULTIME STUDENT NAME T USERNAME TABCOPY NAME TABCOPY1 NAME TABCOPY3 NAME TABCOPY4 NAME TABLEA NAME TABLEA TIME TABLEB TIME TABLEC CNAME TABLED SOMETH TAB_C NAME TONE TNAMETNAME CNAME ------------------------------ ------------------------------ TOPIC USERNAME XS USERNAME XS PUSERNAME23 rows selectedSQL>
这个是字长+3的 SQL> select a.accode, b.accode 2 from asset a, asset b 3 where instr(a.accode, b.accode) = 1 4 and length(a.accode) = length(b.accode) + 3 5 ;
ACCODE ACCODE -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- 001001 001 001001003 001001 001001002 001001 001001002001 001001002 这个是包含的: SQL> select a.accode, b.accode 2 from asset a, asset b 3 where instr(a.accode, b.accode) > 0 4 and a.accode <> b.accode;
查找字段accode 为本是字段accode再加三位的数据
???
select accode from asset where accode like 'accode%' ;
select accode from asset where accode like 'accode%' and length(accode)=9
加长度条件限制就可以
不会当作字段看待
from asset a, asset b
where instr(a.accode, b.accode) = 1
and length(a.accode) = length(b.accode) + 3
明显有逻辑错误。假设你的accode的值是ABC,你要accode like 'ABC___', 当然取不出数据了accode这个字段会自已加上个3位吗?我看永远都不会,条件不成立
如果直接判断b.accode是a.accode的子字符串,且两个不能相同
instr(a.accode, b.accode)>0 and a.accode<>b.accode;
--参考一下:
SQL> select tname,cname from col where cname like '%ME%';TNAME CNAME
------------------------------ ------------------------------
EMPLOYEE FIRST_NAME
EMPLOYEE LAST_NAME
EMPLOYEE1 FIRST_NAME
EMPLOYEE1 LAST_NAME
IP_ADDRESS USERNAME
IP_ADDRESS LOGON_TIME
RECORD MULTIME
STUDENT NAME
T USERNAME
TABCOPY NAME
TABCOPY1 NAME
TABCOPY3 NAME
TABCOPY4 NAME
TABLEA NAME
TABLEA TIME
TABLEB TIME
TABLEC CNAME
TABLED SOMETH
TAB_C NAME
TONE TNAMETNAME CNAME
------------------------------ ------------------------------
TOPIC USERNAME
XS USERNAME
XS PUSERNAME23 rows selectedSQL>
SQL> select a.accode, b.accode
2 from asset a, asset b
3 where instr(a.accode, b.accode) = 1
4 and length(a.accode) = length(b.accode) + 3
5 ;
ACCODE ACCODE
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
001001 001
001001003 001001
001001002 001001
001001002001 001001002
这个是包含的:
SQL> select a.accode, b.accode
2 from asset a, asset b
3 where instr(a.accode, b.accode) > 0
4 and a.accode <> b.accode;
ACCODE ACCODE
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
001001 001
001001002 001
001001002 001001
001001002001 001
001001002001 001001
001001002001 001001002
001001003 001
001001003 001001
001002001001 001
001002001001 001001
10 rows selected