MySQL查詢重復字段,及刪除重復記錄的方法
十載的南宮網(wǎng)站建設(shè)經(jīng)驗,針對設(shè)計、前端、開發(fā)、售后、文案、推廣等六對一服務,響應快,48小時及時工作處理。成都全網(wǎng)營銷推廣的優(yōu)勢是能夠根據(jù)用戶設(shè)備顯示端的尺寸不同,自動調(diào)整南宮建站的顯示方式,使網(wǎng)站能夠適用不同顯示終端,在瀏覽器中調(diào)整網(wǎng)站的寬度,無論在任何一種瀏覽器上瀏覽網(wǎng)站,都能展現(xiàn)優(yōu)雅布局與設(shè)計,從而大程度地提升瀏覽體驗。創(chuàng)新互聯(lián)從事“南宮網(wǎng)站設(shè)計”,“南宮網(wǎng)站推廣”以來,每個客戶項目都認真落實執(zhí)行。
MySQL, 數(shù)據(jù)庫, 數(shù)據(jù)庫, 字段, 服務器
數(shù)據(jù)庫中有個大表,需要查找其中的名字有重復的記錄id,以便比較。如果僅僅是查找數(shù)據(jù)庫中name不重復的字段,很容易:
SELECT min(`id`),`name` FROM `table` GROUP BY `name`;
但是這樣并不能得到說有重復字段的id值。(只得到了最小的一個id值)查詢哪些字段是重復的也容易:
SELECT `name`,count(`name`) as count FROM `table` GROUP BY `name` HAVING count(`name`) 1 ORDER BY count DESC;
但是要一次查詢到重復字段的id值,就必須使用子查詢了,于是使用下面的語句。
SELECT `id`,`name` FROM `table` WHERE `name` in (
SELECT `name`
FROM `table`
GROUP BY `name` HAVING count(`name`) 1);
但是這條語句在mysql中效率太差,感覺mysql并沒有為子查詢生成零時表。于是使用先建立零時表:
create table `tmptable` as (
SELECT `name`
FROM `table`
GROUP BY `name` HAVING count(`name`) 1);
然后使用多表連接查詢:
SELECT a.`id`, a.`name` FROM `table` a, `tmptable` t WHERE a.`name` = t.`name`;
結(jié)果這次結(jié)果很快就出來了。
========================
查詢及刪除重復記錄的方法
(一)
1、查找表中多余的重復記錄,重復記錄是根據(jù)單個字段(peopleId)來判斷
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) 1)
2、刪除表中多余的重復記錄,重復記錄是根據(jù)單個字段(peopleId)來判斷,只留有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、查找表中多余的重復記錄(多個字段)
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) 1)
4、刪除表中多余的重復記錄(多個字段),只留有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、查找表中多余的重復記錄(多個字段),不包含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)
(二)
比方說
在A表中存在一個字段「name」,
而且不同記錄之間的「name」值有可能會相同,
現(xiàn)在就是需要查詢出在該表中的各記錄之間,「name」值存在重復的項;
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
方法二
有兩個意義上的重復記錄,一是完全重復的記錄,也即所有字段均重復的記錄,二是部分關(guān)鍵詞段重復的記錄,比如Name字段重復,而其它字段不一定重復或都重復可以忽略。
1、對于第一種重復,比較容易解決,使用
select distinct * from tableName
就可以得到無重復記錄的結(jié)果集。
如果該表需要刪除重復的記錄(重復記錄保留1條),可以按以下方法刪除
select distinct * into #Tmp from tableName
drop table tableName
select * into tableName from #Tmp
drop table #Tmp
發(fā)生這種重復的原因是表設(shè)計不周產(chǎn)生的,增加唯一索引列即可解決。
2、這類重復問題通常要求保留重復記錄中的第一條記錄,操作方法如下
假設(shè)有重復的字段為Name,Address,要求得到這兩個字段唯一的結(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)
最后一個select即得到了Name,Address不重復的結(jié)果集(但多了一個autoID字段,實際寫時可以寫在select子句中省去此列)
(四)
查詢重復
select * from tablename where id in (
select id from tablename
group by id
having count(id) 1
)
思路:先抽出有重復的C3 C4 C5,然后再和主表相連取出數(shù)據(jù)
解法:按C3 C4 C5 GROUP BY,取出行數(shù)大于1的,即為有重復的C3 C4 C5
SQL語句:
SELECT T1.*
FROM 表名 T1
INNER JOIN
(
SELECT C3,C4,C5
FROM 表名
GROUP BY C3,C4,C5
HAVING COUNT(1) 1
) T2
ON T1.C3 = T2.C3
AND T1.C4 = T2.C4
AND T1.C5 = T2.C5
不好意思不是很懂mysql語句,不知道會不會有的地方語法不同,但猜想方法還是可以用的。
可以通過用該字段分組計數(shù)獲得。例如:
select col1,count(col1) as cnt
from t1 group by col1;
這個查詢可返回表t1的字段col1中每個值的重復次數(shù)。
SELECT
*,?
count(t1.stu_id)?AS?ct
FROM
table?AS?t1
GROUP?BY
t1.stu_id
HAVING
ct??1
//?table?替換為你的表名