SQL語句刪除重複的記錄

1、查找表中多餘的重複記錄，重複記錄是根據單個字段（peopleId）來判斷

select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)

2、刪除表中多餘的重複記錄，重複記錄是根據單個字段（peopleId）來判斷，隻留有rowid最小的記錄

delete from people where peopleName in (select peopleName from people group by peopleName having count(peopleName) > 1) and peopleId not in (select min(peopleId) from people group by peopleName having count(peopleName)>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)

6.消除一個字段的左邊的第一位：

update tableName set [Title]=Right([Title],(len([Title])-1)) where Title like '村%'

7.消除一個字段的右邊的第一位：

update tableName set [Title]=left([Title],(len([Title])-1)) where Title like '%村'

8.假刪除表中多餘的重複記錄（多個字段），不包含rowid最小的記錄

update vitae set ispass=-1 where peopleId in (select peopleId from vitae group by peopleId

一個表中有重複記錄如何用SQL語句查詢出來。。。?

select * from tablename where 重複字段1 in (select 重複字段1 from tablename group by 重複字段1,重複字段2 having count(*)>1)。

SQL重複記錄查詢方法：

1、查找表中多餘的重複記錄，重複記錄是根據單個字段（peopleId）來判斷

select * from people

where peopleId in (select?? peopleId from?? people group by?? peopleId having count (peopleId) > 1)

2、刪除表中多餘的重複記錄，重複記錄是根據單個字段（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

SQL語句查詢如何刪除重複多餘的數據

這個是SQL中distinct的典型用法：

1）從字麵意思就可以了解到：

distinct [dis'ti?kt] adj. 明顯的；獨特的；清楚的；有區別的

2）在SQL中用distinct來消除重複出現的字段值。

使得每個字段值隻出現一次。

具體用法如下：

select distinct 字段名 from 表；

distinct 字段名意思就是隻顯示一次該字段名

一般情況下和order by 結合使用，這樣可以提高效率。

所以這個問題的答案是：select distinct 1,2,3,4 from 表；

1,2,3,4分別代表第一，二，三，四列的字段名，我猜測可能第一列就是每個人的ID,

這樣你把重複的ID過濾留下一個，估計就是你想要的結果了。

希望我的回答能讓您滿意。

SQL查詢語句,怎樣查詢重複數據

selectid,name,memo

fromA

whereidin(selectidfromAgroupbyidhavingcount(1)>=2)

1查詢 abcd相同的記錄：

select * from F where a=b and b=c and c=d

2查詢有重複數據的記錄

select * from F group by a,b,c,d having count(*)>1

3取出數據過濾到重複的數據

select distinct a,b,c,d from f

怎麼利用SQL語句查詢數據庫中具體某個字段的重複行

我一般用這個：

假設懷疑重複的字段名為SeriNo,

select * from [tablename]

group by Se功iNo

having count(SeriNo)<>1

sql語句去重

sql 單表/多表查詢去除重複記錄

單表distinct

多表group by

group by 必須放在 order by 和 limit之前，不然會報錯

************************************************************************************

1、查找表中多餘的重複記錄，重複記錄是根據單個字段（peopleId）來判斷

select * from people

where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)

2、刪除表中多餘的重複記錄，重複記錄是根據單個字段（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”值有可能會相同，

現在就是需要查詢出在該表中的各記錄之間，“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

方法二

＂重複記錄＂有兩個意義上的重複記錄，一是完全重複的記錄，也即所有字段均重複的記錄，二是部分關鍵字段重複的記錄，比如Name字段重複，而其他字段不一定重複或都重複可以忽略。

1、對於第一種重複，比較容易解決，使用

select distinct * from tableName

就可以得到無重複記錄的結果集。

如果該表需要刪除重複的記錄（重複記錄保留1條），可以按以下方法刪除

select distinct * into #Tmp from tableName

drop table tableName

select * into tableName from #Tmp

drop table #Tmp

發生這種重複的原因是表設計不周產生的，增加唯一索引列即可解決。

2、這類重複問題通常要求保留重複記錄中的第一條記錄，操作方法如下

假設有重複的字段為Name,Address，要求得到這兩個字段唯一的結果集

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不重複的結果集（但多了一個autoID字段，實際寫時可以寫在select子句中省去此列）

(四)

查詢重複

select * from tablename where id in (select id from tablename

group by id

having count(id) > 1

)

3、查找表中多餘的重複記錄（多個字段）

select * from vitae a

where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)

運行會產生問題，where(a.peopleId,a.seq)這樣的寫發是通不過的！！！

mysql的sql語句如何去掉重複的數據

難倒隻能分步操作，蛋疼

以下是網友寫的，同樣是坑爹的代碼，我機器上運行不了。

1. 查詢需要刪除的記錄，會保留一條記錄。

select a.id,a.subject,a.RECEIVER from test1 a left join (select c.subject,c.RECEIVER ,max(c.id) as bid from test1 c where status=0 GROUP BY RECEIVER,SUBJECT having count(1) >1) b on a.id< b.bid="" where="" a.subject="b.subject" and="" a.receiver="b.RECEIVER" and="" a.id=""><>

2. 刪除重複記錄，隻保留一條記錄。注意，subject,RECEIVER 要索引，否則會很慢的。

delete a from test1 a, (select c.subject,c.RECEIVER ,max(c.id) as bid from test1 c where status=0 GROUP BY RECEIVER,SUBJECT having count(1) >1) b where a.subject=b.subject and a.RECEIVER = b.RECEIVER and a.id <>

3. 查找表中多餘的重複記錄，重複記錄是根據單個字段（peopleId）來判斷

select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)

4. 刪除表中多餘的重複記錄，重複記錄是根據單個字段（peopleId）來判斷，隻留有rowid最小的記錄

如何用一條SQL語句查詢數據庫重複記錄

方法如下：

select * from 你的表名

a where id=(select min(id) from 你的表名 whereitem_id=a.item_id)

在查詢之前先把數據庫表中的第一行複製到sid裏在去，然後讓sid和下麵的每一行進行比較

取所有相同的行的最小的一下，也可以取最大的，結果是一樣的。

這樣讓所有的行都比較不就得到不重複的數據了。

sql中怎麼刪除兩條重複記錄並保留一條

表結構如下

test (id int,name varchar2,age int)

1 張三 10

2 張三 10

3 李四 20

4 李四 20

5 王五 19

除了id外其他字段全部相同的記錄隻保留一條刪除多餘的記錄

delete test where id not in(select max(id) from test group by name,age)

如果沒有id ,就用rowid

delete test where rowid not in(select max(rowid) from test group by id,name,age) --注意group by後的字段.

sql查詢語句過濾重複數據。

SELECT Id,SiteId,InsertTime,IP,Referrer,Url

FROM

(

SELECT ROW_NUMBER()OVER(PARTITION BY IP ORDER BY Id DESC) number,

Id,SiteId,InsertTime,IP,Referrer,Url

From YourTable

where number = 1

拿走不謝

轉載請注明出處句子大全網 » SQL語句刪除重複的記錄