sql刪除重復項

① access中sql如何去重復數據

access中sql如何去重復數據？現在做自媒體的通常都是一人多號、多平台同步更新，其中一部分都是自有網站或博客的站長，小編就是其中之一。小編就利用當前最盛行的博客程序WordPress搭建起了自己的網站，因為剛開始做自媒體，內容還不夠多，所以用火車頭採集器採集了一些數據來充實自己網站。但是因為在採集過程中沒有驗證標題是否唯一，從而導致出現大量標題重復的文章。
為了解決這個問題，我需要從本地火車頭採集器數據和伺服器WordPress數據中分別刪除重復的數據。火車頭採集器使用的是access資料庫，WordPress使用的是MySQL資料庫。
工具原料電腦互聯網
方法/步驟分步閱讀
1
/6
一、access資料庫刪除重復數據保留唯一記錄
1.打開火車頭本地數據文件SpiderResult.mdb，依次點擊菜單「創建」——「查詢設計」打開查詢設計窗口。
2
/6
2.打開查詢設計窗口會提示添加表，這里不添加所以點擊「關閉」，然後在設計窗口空白處點擊右鍵，選擇「SQL試圖（Q）」。
3
/6

② sql去除重復的項

distinct
b,a,c
------是將b
a
c完全相的記錄只取一條，你的數據里根本沒有完全相同的記錄，所以會全部輸出;
你要的數據可以用下面的sql
select
*
from
(select
t.*,row_number()
over(partition
by
b
order
by
b)
numid
from
table
t)
a
where
a.numid=1;

③ SQL 怎麼去除完全重復的數據

用SQL語句,刪除掉重復項只保留一條

在幾千條記錄里,存在著些相同的記錄,如何能用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語句刪除重復數

--聲明變數
Declare @ID Int, @PosNo Int
Declare @Table Table(
ID Int,
PosNo int
)
Declare cur Cursor Static READ_ONLY For
Select ID, PosNo
From 表
Order By EmpID, ID
Open cur
Fetch From cur Into @ID, @PosNo
While @@FETCH_STATUS = 0
Begin
If Exists(Select * From @Table Where PosNo = @PosNo)
Begin
Delete 表 Where ID = @ID
End
Else
Begin
Insert Into @Table (ID, PosNo)
Values (@ID, @PosNo)
End

Fetch Next From cur Into @ID, @PosNo
End
Close cur
DeAllocate cur

⑤ SQL怎麼去除某一列的重復項

假設存在一個主鍵ID，Name為重復列

--下面這句可以查出所有的沒有重復的數據

select
*
from
表
as
a
where
ID=(select
min(ID)
from
表
where
Name=a.Name)

--根據上面這句就可以刪除所有重復項的數據

delete
from
表
where
ID
not
in(

select ID
from
表
as
a
where
ID=(select
min(ID)
from
表
where
Name=a.Name)

)

好了~

⑥ sql中如何刪除一個表中重復的記錄

sql中刪除一個表中的重復記錄可以採用如下步驟：

1、把a_dist表的記錄用distinct去重，結果放到臨時表中。

select distinct * into #temp from a_dist;

2、把a_dist表的記錄全部刪除。

delete from a_dist;

3、把臨時表中的數據信息導進到a_dist表中，並刪除臨時表。

insert into a_distselect * from #temp;

drop table #temp;

(6)sql刪除重復項擴展閱讀：

SQL (結構化查詢語言)是用於執行查詢的語法。在資料庫上執行的大部分工作都由 SQL 語句完成。SQL 語言包含用於更新、插入和刪除記錄的語法。

增刪改查指令構成了 SQL 的 DML 部分：

• SELECT- 從資料庫表中獲取數據

• UPDATE- 更新資料庫表中的數據

• DELETE- 從資料庫表中刪除數據

• INSERT INTO- 向資料庫表中插入數據

⑦ SQL 刪除列中的重復值

圖片很模糊看不清，你看看下面方法可以嗎

去除表a的重復值
user
mydb
--進入需要修改的資料庫中
select
distinct
*
into
#tmp
from
[a]
--先將數據存入一張臨時表中，剔除重復項
truncate
table
[a]
--然後清空原表
insert
into
[a]
select
*
from
#tmp
--再將臨時表內容插入表a
drop
table
#tmp
--刪除臨時表

你的col001
是一樣的啊，你表裡有主鍵碼，或者自增欄位？

你表裡不算有重復值的啊，雖然前邊一樣，後邊欄位是不一樣的，這樣還算重復？

⑧ 如何在Sql中將重復的所有欄位刪除

用SQL語句,刪除掉重復項只保留一條
在幾千條記錄里,存在著些相同的記錄,如何能用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刪除重復項只保留一條

參考以下：

第一步：查詢出表中的數據數量
第二步：按條件查詢出重復的數據，然後使用循環語句來刪除掉重復的數據

⑩ 如何在Sql中將重復的所有欄位刪除

用SQL語句,刪除掉重復項只保留一條

在幾千條記錄里,存在著些相同的記錄,如何能用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