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