Question

sql怎么查询一列数据的相似度

Answer 1

SQL> create table ttb(name varchar2(10));

Table created.

SQL> insert into ttb values('asdfjkl');

1 row created.

SQL> insert into ttb values('asdkb');

1 row created.

SQL> insert into ttb values('asd323s');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from ttb;

NAME
------------------------------
asdfjkl
asdkb
asd323s

SQL> select wm_concat(name) from ttb;

WM_CONCAT(NAME)
--------------------------------------------------------------------------------
asdfjkl,asdkb,asd323s

SQL> with t as (select to_char(wm_concat(name)) a from ttb) select substr(y.a,1,x.lvl-1) returnvalue from (select distinct(lvl) lvl from (select rn,first_value(lvl) over(order by lvl) lvl,cnt from (select rn,lvl,count(distinct(str)) cnt from (select rn,level lvl,substr(str,1,level) as str from (select rn,str from (select rn,regexp_substr(a,'[^,]+',1,level,'i') as str from (select rownum rn,a from t) connect by level<=length(a)-length(regexp_replace(a,',',''))+1)) connect by level<=length(str)) group by rn,lvl order by lvl asc) where cnt<>1)) x left join t y on 1=1;

RETURNVALUE
--------------------------------------------------------------------------------
asd