sql相似查询
A. sql怎么查询一列数据的相似度
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
B. sql怎么查询一列数据的相似度
with t as (select 'xspasdfdfsdfz,xpaysdfsdss,xparsdfd' a from al)
selectsubstr(y.a,1,x.lvl-1) returnvalue from (selectdistinct(lvl) lvl from (select rn,first_value(lvl) over(orderby 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 (selectrownum rn,a from t) connectby
level<=length(a)-length(regexp_replace(a,',',''))+1)) connectbylevel<=length(str)) groupby rn,lvl orderby lvl asc) where cnt<>1)) x leftjoin t y on1=1;
RETURNVALUE
--------------------------------------------------------------------------------
x
SQL> with t as (select 'xxxxasdfdfsdfz,xxxxxysdfsdss,xxxxxxxxsdfd' a from al)
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
3 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
4 ;
RETURNVALUE
--------------------------------------------------------------------------------
xxxx
这个例子 'xxxxasdfdfsdfz,xxxxxysdfsdss,xxxxxxxxsdfd' 是把这些放在一行里面 如果是一列数据 可以把列转成行
SQL> select * from tt;
ID NAME
---------- ------------------------------
1 aab
1 aac
2 ddca
2 ddcp
2 ddco
SQL> select id,wm_concat(name) newstr from tt group by id;
ID NEWSTR
---------- --------------------
1 aab,aac
2 ddca,ddco,ddcp
这样把最上面的SQL写成函数，直接用函数来处理各分组里面相似度
C. sql 查询一个表内某一字段相似数据
可以用like模糊查询：
select*fromAwherechecklike'我是中国人我来自%'
D. sql查找某一字段相同的所有数据
1、在我们的电脑上打开数据库，这里新建一张含有重复数据的user表做示例。
E. 如何用SQL找出两两相似的数据
如何用SQL找出两两相似的数据
SELECT Name1 AS 姓名,Money1 AS 工资,'缺失' AS 状态 FROM
(SELECT * FROM A left JOIN B ON A.NAME1=B.NAME AND A.Money1=B.Money) AS tb
WHERE Name IS NULL
UNION ALL
SELECT NAME,Money,'失败' FROM B WHERE Money=0
F. SQL中如何查询两表中的相似记录
insert into ##temptable(totcount) values(select sm.content,tmpsm.Col001 from sm, tmpsm where sm.content like '%'tmpsm.Col001'%');
select count(totcount) from ##temptable;
以上是建一个临时表,将查询结果插入进去,然后第二步在计算临时表里的有多少行!
相似就是不完全相等哈
G. sql语句中怎么查询出A表和B表中相似的数据拍在同一行
联表查询就可以, 关键词, leftjoin rightjoin等
H. sql 查询一个表内某一字段相似数据 (在线等)
select A1 from A where A1 like '*相似的字符*'
推荐用“ * ” 此通配符可以代表很多字符并不像“%”只局限一个
I. SQL查询两个相似字段
=null||bqmc!=null||cxmc!=null)
这样改一下：
string str = "select * from 表 where ";
if(lxmc!=null)
{
str += " 字段1 like '%"+lxmc+"%' or 字段2 like '%"+lxmc+"%' or";
}
if(bqmc!=null)
{
str += " 字段1 like '%"+bqmc+"%' or 字段2 like '%"+bqmc+"%' or";
}
if(cxmc!=null)
{
str += " 字段1 like '%"+cxmc+"%' or 字段2 like '%"+cxmc+"%' or";
}
if (str.Substring(str.Length - 2).Equals("or"))
{
str = str.Substring(0, str.Length - 2);
}
这样如果三个参数一个都没有，那就查询全部，如果有一个，那就判断字段1和字段2是不是都有like的值，如果三个都写了，就三个都判断一次
J. sql 怎么查询与条件80%相似的数据
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