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(*)>
❷ SQL查詢，如何去除重復的記錄
首先，先說明一個問題。這樣的結果出現，說明系統設計是有問題的。
其次
刪除重復數據，你要提供你是什麼資料庫。
不同資料庫會有不同的解決方案。
關鍵字Distinct
去除重復，如下列SQL，去除Test相同的記錄；
1.
select
distinct
Test
from
Table
2.
如果是要刪除表中存在的重復記錄，那就邏輯處理，如下：
3.
select
Test
from
Table
group
by
Test
having
count（test)>1
4.
先查詢存在重復的數據，後面根據條件刪除
還有一個更簡單的方法可以嘗試一下：
select
aid,
count(distinct
uid)
from
表名
group
by
aid
這是sqlserver
的寫法。
如圖一在數據表中有兩個膀胱沖洗重復的記錄。
2
可以通過sql語句「select
*from
表名
where
編碼
in(select
編碼
from
表名
group
by
編碼
having
count(1)
>=
2)」來查詢出變種所有重復的記錄如圖二
3
通過sql語句"
delete
from
表名
where
編碼
in(select
編碼
from
表名
group
by
編碼
having
count(1)
>=
2)
and
編碼
not
in
(select
max(編碼)from
表名
group
by
編碼
having
count(1)
>=2)
"來刪除重復的記錄只保留編碼最大的記錄
❸ SQL裡面如何刪除重復的記錄
方法1 最簡單的方法，拿出唯一的記錄集，放入中間表。原表清空，再把數據導回來。數據少的話很快。
select distinct UPPER(Stu_ID),*from tableName
方法2 如果這個表特別大，導表的方法速度受不了的話：
找出所有重復記錄
select * from tableName where Stu_ID_UP in (
select Stu_ID_UPfrom (select UPPER(Sut_ID) Stu_ID_UP from tableName)
group by Stu_ID_UP
having count(Stu_ID_UP) > 1）然後人工查看一下這些數據，推測這些重復數據產生的原因，是輸入錯誤、代碼邏輯錯誤、還是業務邏輯錯誤。要是業務原因，那一定要跟業務部門談好解決方案，別刪完出狀況。
經過第2步的各種確認刪除方法後，開始刪~
保留rowid最小的記錄
delete from tableName a
where (a.Stu_ID) in (
select Stu_ID_UP
from (
select UPPER(Sut_ID) Stu_ID_UP
from tableName)
group by Stu_ID_UP
having count(Stu_ID_UP) > 1)
and rowid not in (select min(rowid)
from (select UPPER(Sut_ID) Stu_ID_UP from tableName)
group by Stu_ID_UP
having count(Stu_ID_UP) > 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查詢去掉重復記錄
1、打開要去掉重復數據的資料庫，這里新建一張含有重復數據的user表做示例，如下圖所示：
❻ sql中怎麼刪除兩條重復記錄並保留一條
將數據去重復後暫存到臨時表#a中
selectdistinct*into#afromtable1where條件
deletetable1where刪除限制條件
insertintotable1select*from#a-將暫存的數據插回資料庫
droptable#a-刪除臨時表
註：當前的資料庫，每一個表都應該有一個標志欄位，以保證記錄不完全重復，否則實用中極易出問題。
(6)sql刪除相同記錄擴展閱讀：
SQL語句刪除掉重復的其他情況
1、查找表中多餘的重復記錄，重復記錄是根據單個欄位（peopleId）來判斷
SELECT
*
FROM
people
WHERE
peopleId IN (
SELECT
peopleId
FROM
people
GROUP BY
peopleId
HAVING
count(peopleId) > 1
)
2、查找表中多餘的重復記錄（多個欄位）
SELECT
*
FROM
vitae a
WHERE
(a.peopleId, a.seq) IN (
SELECT
peopleId,
seq
FROM
vitae
GROUP BY
peopleId,
seq
HAVING
count(*) > 1
)
參考資料來源：結構化查詢語言（SQL）-網路
❼ SQL刪除重復
可新建一個臨時表tmp來保存去重後的結果，具體mysql代碼如下：
❽ sql中怎麼將重復的記錄去掉
方法一按照多條件重復處理：
delete tmp from(
select row_num = row_number() over(partition by 欄位，欄位 order by 時間 desc)
from 表 where 時間> getdate()-1
) tmp
where row_num > 1
方法二按照單一條件進行去重：
delete from 表 where 主鍵ID not in(
select max(主鍵ID) from 表 group by 需要去重的欄位 having count(需要去重的欄位)>=1
)
注意：為提高效率如上兩個方法都可以使用臨時表， not in 中的表可以先提取臨時表#tmp，
然後採用not exists來執行，為避免數量過大，可批量用Top控制刪除量
delete top(2) from 表
where not exists (select 主鍵ID
from #tmp where #tmp.主鍵ID=表.主鍵ID)
❾ SQL語句刪除重復的記錄
刪除重復的數據
delete from tb where id not in (
select id from
(select fileSize,fileName ,max(id) id from tb group by filesize,filename ) a
)
現在完成了重復數據的刪除，主要是利用了找出某個分組中最大的那個id，其中包括了所有不重復的id，然後使用not in將需要保留的排除。