sql排除相同數據
⑴ sql去除重復的項
首先你是要去除，也就是說刪除數據，還是只是查詢不重復數據，如果是刪除重復數據
就不該用select；暫且認為
你是查詢數據；
其次，你的意思應該是去除
b
列中重復的數據，但是你沒給定
確切的條件，比如，b列出現了重復數據，那你是要保留那一條呢，
再就是你那個查詢：select
distinct
b,a,c
from
table
的意思是將三列
都重復的去除，只有三列都重復了
才會去除。你這里的數據明顯
不存在三個列都重復的數據，所以查詢
出來的還是原
表中的數據。
⑵ 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語句怎麼刪除重復的數據
刪除重復的數據
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將需要保留的排除。
⑷ 如何用SQL語句刪除兩個表中相同的記錄
1，首先創建一個表，並在表中插入重復的記錄，如下圖所示。
⑸ sql查詢語句怎麼排除重復數據
select
id,
name,
memo
from
A
where
id
in
(select
id
from
A
group
by
id
having
count(1)
>=
2)
select
id,
name,
memo
from
A
where
id
in
(select
id
from
A
group
by
id
having
count(1)
>=
2)
⑹ sql 怎樣刪除一列中相同的數據
sql清除一列數據分為兩種情況，一種是將一列的數據清空，另一種是將某列名刪除。
工具：SQL
Server
2008
R2
表中數據如下：
一、將數據清空（刪除begin_date列的數據，使之為空）
update
test
set
begin_date=null;
執行後結果：
二、將列名刪除（刪除begin_date列，使之在表中不存在）
alter
table
test
drop
column
begin_date;
執行後結果（可發現begin_date列已經刪除）：
⑺ sql 如何過濾重復記錄
問題背景
在一個多表查詢的sql中正常情況下產生的數據都是唯一的，但因為資料庫中存在錯誤(某張表中存在相同的外鍵ID)導致我這邊查詢出來的數據就會有重復的問題
下面結果集中UserID：15834存在多個
參考：
MSDN: OVER 子句 (Transact-SQL)
stackoverflow sql query distinct with Row_Number
SQL Trick: row_number() is to SELECT what dense_rank() is to SELECT DISTINCT
⑻ 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(*)>
⑼ 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)