sql去除重復的數據

⑴ 在sql語言中去掉重復值的命令是

distinct。
SQLserver中很明顯的去重復的語句是distinct。selectdistinct是去除重復的記錄行，count（distinctColumn），消除重復值。還有一些不明顯的具有去重功能的詞，例如union，會去除重復的記錄行或值。

⑵ SQL查詢中如何剔除重復

1，存在兩條完全相同的紀錄

這是最簡單的一種情況，用關鍵字distinct就可以去掉

example： select distinct * from table(表名) where (條件)

2，存在部分欄位相同的紀錄（有主鍵id即唯一鍵）

如果是這種情況的話用distinct是過濾不了的，這就要用到主鍵id的唯一性特點及group by分組

example:

select * from table where id in (select max(id) from table group by [去除重復的欄位名列表,....])

3，沒有唯一鍵ID

example:

select identity(int1,1) as id,* into newtable(臨時表) from table

select * from newtable where id in (select max(id) from newtable group by [去除重復的欄位名列表,....])

drop table newtable

(2)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 count(*) > 1)

⑶ SQL查詢，如何去除重復的記錄

sql查詢去除重復值語句x0dx0asql 單表/多表查詢去除重復記錄x0dx0a單表distinctx0dx0ax0dx0a多表group byx0dx0ax0dx0agroup by 必須放在 order by 和 limit之前，不然會報錯x0dx0ax0dx0a************************************************************************************x0dx0ax0dx0a1、查找表中多餘的重復記錄，重復記錄是根據單個欄位（peopleId）來判斷x0dx0ax0dx0aselect * from peoplex0dx0ax0dx0awhere peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)x0dx0a2、刪除表中多餘的重復記錄，重復記錄是根據單個欄位（peopleId）來判斷，只留有rowid最逗臘飢小的記山返錄x0dx0ax0dx0adelete from peoplex0dx0awhere peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)x0dx0aand rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)x0dx0a3、查找表中多餘的重復記錄（多個欄位）x0dx0ax0dx0aselect * from vitae ax0dx0awhere (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)x0dx0a4、刪除表中局鎮多餘的重復記錄（多個欄位），只留有rowid最小的記錄x0dx0adelete from vitae ax0dx0awhere (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)x0dx0aand rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)x0dx0a5、查找表中多餘的重復記錄（多個欄位），不包含rowid最小的記錄x0dx0ax0dx0aselect * from vitae ax0dx0awhere (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)x0dx0aand rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>

⑷ sql如何刪除重復數據

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(*)>