|
查找數(shù)據(jù)庫(kù)中重復(fù)數(shù)據(jù)T-SQL ========第一篇========= 在一張表中某個(gè)字段下面有重復(fù)記錄，有很多方法，但是有一個(gè)方法，是比較高效的，如下語(yǔ)句： select data_guid from adam_entity_datas a where a.rowid > (select min(b.rowid) from adam_entity_datas b where b.data_guid = a.data_guid) 如果表中有大量數(shù)據(jù)，但是重復(fù)數(shù)據(jù)比較少，那么可以用下面的語(yǔ)句提高效率 select data_guid from adam_entity_datas where data_guid in (select data_guid from adam_entity_datas group by data_guid having count(*) > 1) 此方法查詢(xún)出所有重復(fù)記錄了，也就是說(shuō)，只要是重復(fù)的就選出來(lái)，下面的語(yǔ)句也許更高效 select data_guid from adam_entity_datas where rowid in (select rid from (select rowid rid,row_number()over(partition by data_guid order by rowid) m from adam_entity_datas) where m <> 1) 目前只知道這三種比較有效的方法。 第一種方法比較好理解，但是最慢，第二種方法最快，但是選出來(lái)的記錄是所有重復(fù)的記錄，而不是一個(gè)重復(fù)記錄的列表，第三種方法，我認(rèn)為最好。 ========第二篇========= select usercode,count(*) from ptype group by usercode having count(*) >1 ========第三篇========= 找出重復(fù)記錄的ID: select ID from ( select ID ,count(*) as Cnt from 要消除重復(fù)的表 group by ID ) T1 where T1.cnt>1 刪除數(shù)據(jù)庫(kù)中重復(fù)數(shù)據(jù)的幾個(gè)方法 數(shù)據(jù)庫(kù)的使用過(guò)程中由于程序方面的問(wèn)題有時(shí)候會(huì)碰到重復(fù)數(shù)據(jù)，重復(fù)數(shù)據(jù)導(dǎo)致了數(shù)據(jù)庫(kù)部分設(shè)置不能正確設(shè)置…… 方法一 declare @max integer,@id integer declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) > 1 open cur_rows fetch cur_rows into @id,@max while @@fetch_status=0 begin select @max = @max -1 set rowcount @max delete from 表名 where 主字段 = @id fetch cur_rows into @id,@max end close cur_rows set rowcount 0 方法二 有兩個(gè)意義上的重復(fù)記錄，一是完全重復(fù)的記錄，也即所有字段均重復(fù)的記錄，二是部分關(guān)鍵字段重復(fù)的記錄，比如Name字段重復(fù)，而其他字段不一定重復(fù)或都重復(fù)可以忽略。 1、對(duì)于第一種重復(fù)，比較容易解決，使用 select distinct * from tableName 就可以得到無(wú)重復(fù)記錄的結(jié)果集。 如果該表需要?jiǎng)h除重復(fù)的記錄，可以按以下方法刪除 select distinct * into #Tmp from tableName drop table tableName select * into tableName from #Tmp drop table #Tmp 2、這類(lèi)重復(fù)問(wèn)題通常要求保留重復(fù)記錄中的第一條記錄，*作方法如下 假設(shè)有重復(fù)的字段為Name,Address，要求得到這兩個(gè)字段唯一的結(jié)果集 select identity(int,1,1) as autoID, * into #Tmp from tableName select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID select * from #Tmp where autoID in(select autoID from #tmp2) 最后一個(gè)select即得到了Name，Address不重復(fù)的結(jié)果集 更改數(shù)據(jù)庫(kù)中表的所屬用戶(hù)的兩個(gè)方法 大家可能會(huì)經(jīng)常碰到一個(gè)數(shù)據(jù)庫(kù)備份還原到另外一臺(tái)機(jī)器結(jié)果導(dǎo)致所有的表都不能打開(kāi)了，原因是建表的時(shí)候采用了當(dāng)時(shí)的數(shù)據(jù)庫(kù)用戶(hù)…… ========第四篇========= 如何查詢(xún)數(shù)據(jù)庫(kù)中的重復(fù)記錄？ 比如說(shuō)有個(gè)表中的數(shù)據(jù)是這樣： --------- a a a b b c --------- 查詢(xún)出的結(jié)果是： 記錄 數(shù)量 a 3 b 2 c 1 怎樣寫(xiě)這個(gè)SQL語(yǔ)句？ ----------------------- select distinct(name),count(*) from tabname group by name; ------------------------------------- 想出來(lái)了，這樣就可以排序了。 select a1,count(a1) as total from tablename group by a1 order by total desc -------------------------------------- select distinct(a1),count(a1) as total from tablename group by a1 order by total desc 加個(gè)distinct更有效率 -------------------------------------------------------------- select p.*, m.* from table1 p left join table2 m on p.item1=m.item2 where p.item3='#$#@%$@' order by p.item3 asc limit 10 就類(lèi)似這么寫(xiě) ========第五篇========= 如何查找數(shù)據(jù)庫(kù)中的重復(fù)記錄? 能在Access中用的方法 ---------------------------------------------------------------------- select * from 表 A inner join (select 字段1,字段2 from 表 group by 字段1,字段2 having Count(*)>1) B on A.字段1=B.字段1 and A.字段2=B.字段2 -------------------------------------------------------- 問(wèn)題： 根據(jù)其中幾個(gè)字段判斷重復(fù)，只保留一條記錄，但是要顯示全部字段，怎么查詢(xún)，謝謝??！ 比如 字段1 字段2 字段3 字段4 a b c 1 a b c 1 a b d 2 a b d 3 b b d 2 想得到的結(jié)果為 a b c 1 a b d 2（或者3） b b d 2 說(shuō)明，根據(jù)字段1，2,3組合不重復(fù)，字段4 不考慮，得到了3個(gè)記錄 但是也要顯示字段4。 方法一： 可以用臨時(shí)表的方法來(lái)解決： CurrentProject.Connection.Execute "drop table temptable" CurrentProject.Connection.Execute "select * into temptable from 表2 where 1=2" CurrentProject.Connection.Execute "insert into temptable(字段1,字段2,字段3) SELECT DISTINCT 表2.字段1, 表2.字段2, 表2.字段3 FROM 表2;" CurrentProject.Connection.Execute "UPDATE temptable INNER JOIN 表2 ON (表2.字段1 = temptable.字段1) AND (表2.字段2 = temptable.字段2) AND (表2.字段3 = temptable.字段3) SET temptable.字段4 = [表2].[字段4];" 方法二： 可以直接使用一個(gè)SELECT查詢(xún)篩選出需要的數(shù)據(jù)： 可以假定第四字段都選值最小的 SELECT [1],[2], [3], Min([4]) AS Min4 FROM 表1 GROUP BY 表1.[1], 表1.[2], 表1.[3]; 問(wèn)題： 表2 id NAME r1 r2 1 1 w ee 1 1 1 1232 1 2 123 123 1 2 12 434 1 2 123 123 2 1 123 123 ID 為數(shù)值，NAME 為字符。每條記錄沒(méi)有唯一標(biāo)識(shí)。 要求取得 ID 和 NAME 合并后不重復(fù)的記錄，如有重復(fù)保留其中一條即可，但要顯示所有記錄。 回答： SELECT a.*, (select top 1 r1 from 表2 as a1 where a1.id=a.id and a1.name=a.name) AS r1, (select top 1 r2 from 表2 as a2 where a2.id=a.id and a2.name=a.name) AS r2 FROM [SELECT DISTINCT 表2.id, 表2.NAME FROM 表2]. AS a; SELECT a.*, dlookup("r1","表2","id=" & a.id & " and name='"& a.name & "'") AS r1, dlookup("r2","表2","id=" & a.id & " and name='"& a.name & "'") AS r2 FROM [SELECT DISTINCT 表2.id, 表2.NAME FROM 表2]. AS a; 注意，上述代碼中由于沒(méi)有唯一標(biāo)識(shí)列，因此顯示的 R1 R2 的先后次序無(wú)從確定，一般是按輸入的先后順序，但是微軟沒(méi)有官方資料說(shuō)明到底按哪個(gè)順序，請(qǐng)網(wǎng)友注意。 請(qǐng)注意，上述表2為沒(méi)有唯一標(biāo)識(shí)字段，如果現(xiàn)在再建立一個(gè)自動(dòng)編號(hào)字段“主鍵”則可以用以下代碼 SELECT a.ID, a.name, b.r1, b.r2, b.主鍵 FROM (SELECT 表2.id, 表2.NAME, Min(表2.主鍵) AS 主鍵 FROM 表2 GROUP BY 表2.id, 表2.NAME) AS a inner JOIN 表2 AS b ON a.主鍵=b.主鍵; ========第六篇========= 1.查詢(xún)數(shù)據(jù)庫(kù)中重復(fù)的記錄: select realname,count(*) from users group by realname having count(*)>1 ========第七篇========= SELECT T0.ItemCode, T0.ItemName FROM OITM T0 WHERE exists (select 1 from OITM A where A.CODEBARS = TO.CODEBARS And A.ItemCode < > TO.ItemCode) ========第八篇========= 相信很多人在查詢(xún)數(shù)據(jù)庫(kù)時(shí)都會(huì)碰到檢索某表中不重復(fù)記錄的時(shí)候,提到檢索不重復(fù)記錄,馬上想到的肯定是Distinct或者Group By分組, 小弟在初次使用的時(shí)候碰到了一些麻煩,這里拿出來(lái)與大家分享,希望對(duì)更多的朋友有所幫助! 先看看數(shù)據(jù)庫(kù)表結(jié)構(gòu)： 表名: TEST 字段: Id,A,B,C,D 其中B字段包含重復(fù)值; Id A B C D 1 11 a 34 bvb 2 22 a 35 fgfg 3 33 d ht sdf 4 44 a 345 de 5 55 c sfsf sscv 6 66 b rt fg 下面我們來(lái)看看用什么樣的SQL語(yǔ)句檢索出不含重復(fù)記錄的數(shù)據(jù): 使用Distinct關(guān)鍵字 Distinct關(guān)鍵字主要用來(lái)在SELECT查詢(xún)記錄中根據(jù)某指定字段的值去除重復(fù)記錄 SELECT DISTINCT [字段名] FROM [表名] WHERE [檢索條件字句] 所以用這樣一句SQL就可以去掉重復(fù)項(xiàng)了: [color=]SELECT DISTINCT (B) FROM TEST 但是: 這里有一個(gè)非常非常需要注意的地方： SELECT DISTINCT [字段名]后面不能再跟其他的字段，否則檢索出來(lái)的記錄仍然會(huì)含有重復(fù)項(xiàng)； 錯(cuò)誤寫(xiě)法： SELECT DISTINCT [字段名] ，[其他字段名] FROM [表名] WHERE [檢索條件字句] 實(shí)際上,我們上面SQL語(yǔ)句結(jié)果集里就只有B字段;(一般情況下,這種結(jié)果應(yīng)該是很難滿(mǎn)足需求的) 如果我們的記錄集里還需要有其他字段值,那怎么辦呢? 實(shí)際上,我們完全可以用另一種辦法來(lái)解決問(wèn)題;只是需要用到子查詢(xún)而已! 使用GROUP BY 分組 有一點(diǎn)需要注意: 使用帶有GROUP BY字句的查詢(xún)語(yǔ)句時(shí),在SELECT列表指定的列要么是GROUP BY 指定的列,要么包含聚合組函數(shù) 所以用這樣一句SQL就可以去掉重復(fù)項(xiàng)了: [color=]SELECT * FROM TEST WHERE id in (SELECT MIN(id) FROM TEST GROUP BY B) 這樣就得到我們想要的結(jié)果集了: Id A B C D 1 11 a 34 bvb 3 33 d ht sdf 5 55 c sfsf sscv 6 66 b rt fg ========第九篇======mysql=== ---------------------------------------------------------------------- 我的mysql表中的賬號(hào)是8位的隨機(jī)數(shù),我現(xiàn)在想查賬號(hào)有沒(méi)有重復(fù)的,應(yīng)該怎樣操作, ---------------------------------------------------------------------- select count(*) as num,賬號(hào) from TABLE GROUP BY 賬號(hào) num > 1 就有重復(fù)! ========第十篇====（著急的人直接看紅字）===== 在使用mysql時(shí)，有時(shí)需要查詢(xún)出某個(gè)字段不重復(fù)的記錄，雖然mysql提供有distinct這個(gè)關(guān)鍵字來(lái)過(guò)濾掉多余的重復(fù)記錄只保留一條，但往往只用它來(lái)返回不重復(fù)記錄的條數(shù)，而不是用它來(lái)返回不重記錄的所有值。其原因是distinct只能返回它的目標(biāo)字段，而無(wú)法返回其它字段，這個(gè)問(wèn)題讓我困擾了很久，用distinct不能解決的話(huà)，我只有用二重循環(huán)查詢(xún)來(lái)解決，而這樣對(duì)于一個(gè)數(shù)據(jù)量非常大的站來(lái)說(shuō)，無(wú)疑是會(huì)直接影響到效率的。所以我花了很多時(shí)間來(lái)研究這個(gè)問(wèn)題，網(wǎng)上也查不到解決方案，期間把容容拉來(lái)幫忙，結(jié)果是我們兩人都郁悶了。。。。。。。。。 下面先來(lái)看看例子： table id name 1 a 2 b 3 c 4 c 5 b 庫(kù)結(jié)構(gòu)大概這樣，這只是一個(gè)簡(jiǎn)單的例子，實(shí)際情況會(huì)復(fù)雜得多。 比如我想用一條語(yǔ)句查詢(xún)得到name不重復(fù)的所有數(shù)據(jù)，那就必須使用distinct去掉多余的重復(fù)記錄。 select distinct name from table 得到的結(jié)果是: name a b c 好像達(dá)到效果了，可是，我想要得到的是id值呢？改一下查詢(xún)語(yǔ)句吧: select distinct name, id from table 結(jié)果會(huì)是: id name 1 a 2 b 3 c 4 c 5 b distinct怎么沒(méi)起作用？作用是起了的，不過(guò)他同時(shí)作用了兩個(gè)字段，也就是必須得id與name都相同的才會(huì)被排除。。。。。。。 我們?cè)俑母牟樵?xún)語(yǔ)句: select id, distinct name from table 很遺憾，除了錯(cuò)誤信息你什么也得不到，distinct必須放在開(kāi)頭。難到不能把distinct放到where條件里？能，照樣報(bào)錯(cuò)。。。。。。。 很麻煩吧？確實(shí)，費(fèi)盡心思都沒(méi)能解決這個(gè)問(wèn)題。沒(méi)辦法，繼續(xù)找人問(wèn)。 拉住公司里一JAVA程序員，他給我演示了oracle里使用distinct之后，也沒(méi)找到mysql里的解決方案，最后下班之前他建議我試試group by。 試了半天，也不行，最后在mysql手冊(cè)里找到一個(gè)用法，用group_concat(distinct name)配合group by name實(shí)現(xiàn)了我所需要的功能，興奮，天佑我也，趕快試試。 報(bào)錯(cuò)。。。。。。。。。。。。郁悶。。。。。。。連mysql手冊(cè)也跟我過(guò)不去，先給了我希望，然后又把我推向失望，好狠哪。。。。 再仔細(xì)一查，group_concat函數(shù)是4.1支持，暈，我4.0的。沒(méi)辦法，升級(jí)，升完級(jí)一試，成功。。。。。。 終于搞定了，不過(guò)這樣一來(lái)，又必須要求客戶(hù)也升級(jí)了。 突然靈機(jī)一閃，既然可以使用group_concat函數(shù)，那其它函數(shù)能行嗎？ 趕緊用count函數(shù)一試，成功，我。。。。。。。想哭啊，費(fèi)了這么多工夫。。。。。。。。原來(lái)就這么簡(jiǎn)單。。。。。。 現(xiàn)在將完整語(yǔ)句放出: select *, count(distinct name) from table group by name 結(jié)果: id name count(distinct name) 1 a 1 2 b 1 3 c 1 最后一項(xiàng)是多余的，不用管就行了，目的達(dá)到。。。。。 唉，原來(lái)mysql這么笨，輕輕一下就把他騙過(guò)去了，郁悶也就我吧（對(duì)了，還有容容那家伙），現(xiàn)在拿出來(lái)希望大家不要被這問(wèn)題折騰。 哦，對(duì)，再順便說(shuō)一句，group by 必須放在 order by 和 limit之前，不然會(huì)報(bào)錯(cuò)，差不多了，發(fā)給容容放網(wǎng)站上去，我繼續(xù)忙碌。。。。。。 ----------------------------------------------------------------------------------------- 更郁悶的事情發(fā)生了，在準(zhǔn)備提交時(shí)容容發(fā)現(xiàn)，有更簡(jiǎn)單的解決方法。。。。。。 select id, name from table group by name select * from table group by name ========第十一篇========= 查詢(xún)及刪除重復(fù)記錄的方法 (一) 1、查找表中多余的重復(fù)記錄，重復(fù)記錄是根據(jù)單個(gè)字段（peopleId）來(lái)判斷 select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) 2、刪除表中多余的重復(fù)記錄，重復(fù)記錄是根據(jù)單個(gè)字段（peopleId）來(lái)判斷，只留有rowid最小的記錄 delete from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1) 3、查找表中多余的重復(fù)記錄（多個(gè)字段） select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) 4、刪除表中多余的重復(fù)記錄（多個(gè)字段），只留有rowid最小的記錄 delete from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1) 5、查找表中多余的重復(fù)記錄（多個(gè)字段），不包含rowid最小的記錄 select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1) (二) 比方說(shuō) 在A表中存在一個(gè)字段“name”， 而且不同記錄之間的“name”值有可能會(huì)相同， 現(xiàn)在就是需要查詢(xún)出在該表中的各記錄之間，“name”值存在重復(fù)的項(xiàng)； Select Name,Count(*) From A Group By Name Having Count(*) > 1 如果還查性別也相同大則如下: Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 1 (三) 方法一 declare @max integer,@id integer declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) >； 1 open cur_rows fetch cur_rows into @id,@max while @@fetch_status=0 begin select @max = @max -1 set rowcount @max delete from 表名 where 主字段 = @id fetch cur_rows into @id,@max end close cur_rows set rowcount 0 方法二 有兩個(gè)意義上的重復(fù)記錄，一是完全重復(fù)的記錄，也即所有字段均重復(fù)的記錄，二是部分關(guān)鍵字段重復(fù)的記錄，比如Name字段重復(fù)，而其他字段不一定重復(fù)或都重復(fù)可以忽略。 1、對(duì)于第一種重復(fù)，比較容易解決，使用 select distinct * from tableName 就可以得到無(wú)重復(fù)記錄的結(jié)果集。 如果該表需要?jiǎng)h除重復(fù)的記錄（重復(fù)記錄保留1條），可以按以下方法刪除 select distinct * into #Tmp from tableName drop table tableName select * into tableName from #Tmp drop table #Tmp 發(fā)生這種重復(fù)的原因是表設(shè)計(jì)不周產(chǎn)生的，增加唯一索引列即可解決。 2、這類(lèi)重復(fù)問(wèn)題通常要求保留重復(fù)記錄中的第一條記錄，操作方法如下 假設(shè)有重復(fù)的字段為Name,Address，要求得到這兩個(gè)字段唯一的結(jié)果集 select identity(int,1,1) as autoID, * into #Tmp from tableName select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID select * from #Tmp where autoID in(select autoID from #tmp2) 最后一個(gè)select即得到了Name，Address不重復(fù)的結(jié)果集（但多了一個(gè)autoID字段，實(shí)際寫(xiě)時(shí)可以寫(xiě)在select子句中省去此列） (四) 查詢(xún)重復(fù) select * from tablename where id in ( select id from tablename group by id having count(id) > 1 )
|
|
|
來(lái)自： jolilfj > 《數(shù)據(jù)庫(kù)》
