SQL Server刪除表中的重復(fù)數(shù)據(jù)
添加示例數(shù)據(jù)
create table Student(ID varchar(10) not null,Name varchar(10) not null,);insert into Student values("1", "zhangs");insert into Student values("2", "zhangs");insert into Student values("3", "lisi");insert into Student values("4", "lisi");insert into Student values("5", "wangwu");
刪除Name重復(fù)多余的行,每個(gè)Name僅保留1行數(shù)據(jù)
1、查詢表中Name 重復(fù)的數(shù)據(jù)
select Name from Student group by Name having count(Name) > 1
2、有唯一列,通過(guò)唯一列最大或最小方式刪除重復(fù)記錄
檢查表中是否有主鍵或者唯一值的列,當(dāng)前可以數(shù)據(jù)看到ID是唯一的,可以通過(guò)Name分組排除掉ID最大或最小的行
delete from Student where Name in( select Name from Student group by Name having count(Name) > 1) and ID not in(select max(ID) from Student group by Name having count(Name) > 1 )
執(zhí)行刪除腳本后查詢
3、無(wú)唯一列使用ROW_NUMBER()函數(shù)刪除重復(fù)記錄
如果表中沒(méi)有唯一值的列,可以通過(guò)row_number 來(lái)刪除重復(fù)數(shù)據(jù)
重復(fù)執(zhí)行插入腳本,查看表數(shù)據(jù),表中沒(méi)有唯一列值
Delete T From (Select Row_Number() Over(Partition By [Name] order By [ID]) As RowNumber,* From Student)T Where T.RowNumber > 1
小知識(shí)點(diǎn)
語(yǔ)法:ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)
表示根據(jù)COLUMN分組,在分組內(nèi)部根據(jù) COLUMN排序,而此函數(shù)計(jì)算的值就表示每組內(nèi)部排序后的順序編號(hào)(組內(nèi)連續(xù)的唯一的)
函數(shù)“Row_Number”必須有 OVER 子句。OVER 子句必須有包含 ORDER BY
Row_Number() Over(Partition By [Name] order By [ID]) 表示已name列分組,在每組內(nèi)以ID列進(jìn)行升序排序,每組內(nèi)返回一個(gè)唯一的序號(hào)
執(zhí)行刪除腳本后查詢表數(shù)據(jù)
到此這篇關(guān)于SQL Server刪除表中重復(fù)數(shù)據(jù)的文章就介紹到這了。希望對(duì)大家的學(xué)習(xí)有所幫助,也希望大家多多支持。
相關(guān)文章:
1. SQL Server中的邏輯函數(shù)介紹2. SQL Server解析/操作Json格式字段數(shù)據(jù)的方法實(shí)例3. SQL SERVER數(shù)據(jù)庫(kù)開發(fā)之存儲(chǔ)過(guò)程的應(yīng)用4. SQL Server全文檢索簡(jiǎn)介5. idea連接SQL Server數(shù)據(jù)庫(kù)的詳細(xì)圖文教程6. SQL Server主鍵與外鍵設(shè)置以及相關(guān)理解7. SQL Server使用CROSS APPLY與OUTER APPLY實(shí)現(xiàn)連接查詢8. SQL Server如何建表的詳細(xì)圖文教程9. SQL Server數(shù)據(jù)庫(kù)連接查詢和子查詢實(shí)戰(zhàn)案例10. SQL Server中搜索特定的對(duì)象
