database

« | »

在SQL Server中快速删除重复记录

databaseadmin | 17 七月, 2006 07:39

　　开发人员的噩梦——删除重复记录

　　想必每一位开发人员都有过类似的经历，在对数据库进行查询或统计的时候不时地会碰到由于表中存在重复的记录而导致查询和统计结果不准确。解决该问题的办法就是将这些重复的记录删除，只保留其中的一条。

　　在SQL Server中除了对拥有十几条记录的表进行人工删除外，实现删除重复记录一般都是写一段代码，用游标的方法一行一行检查，删除重复的记录。因为这种方法需要对整个表进行遍历，所以对于表中的记录数不是很大的时候还是可行的，如果一张表的数据达到上百万条，用游标的方法来删除简直是个噩梦，因为它会执行相当长的一段时间。

　　四板斧——轻松消除重复记录

　　殊不知在SQL Server中有一种更为简单的方法，它不需要用游标，只要写一句简单插入语句就能实现删除重复记录的功能。为了能清楚地表述，我们首先假设存在一个产品信息表Products，其表结构如下：



CREATE TABLE Products (
ProductID int,
ProductName nvarchar (40),
Unit char(2),
UnitPrice money
)

　　假设产品Chang和Tofu的记录在产品信息表中存在重复。现在要删除这些重复的记录，只保留其中的一条。步骤如下：

　　第一板斧——建立一张具有相同结构的临时表



CREATE TABLE Products_temp (
ProductID int,
ProductName nvarchar (40),
Unit char(2),
UnitPrice money
)

　　第二板斧——为该表加上索引，并使其忽略重复的值
　　方法是在企业管理器中找到上面建立的临时表Products _temp，单击鼠标右键，选择所有任务，选择管理索引，选择新建。然后设置索引选项。

　　第三板斧——拷贝产品信息到临时表



insert into Products_temp Select * from Products

　　此时SQL Server会返回如下提示：

　　服务器: 消息 3604，级别 16，状态 1，行 1

　　已忽略重复的键。

　　它表明在产品信息临时表Products_temp中不会有重复的行出现。

　　第四板斧——将新的数据导入原表
　　将原产品信息表Products清空，并将临时表Products_temp中数据导入，最后删除临时表Products_temp。



delete Products
insert into Products select * from Products_temp
drop table Products_temp

　　这样就完成了对表中重复记录的删除。无论表有多大，它的执行速度都是相当快的，而且因为几乎不用写语句，所以它也是很安全的。

　　小提示：上述方法中删除重复记录取决于创建唯一索引时选择的字段，在实际的操作过程中读者务必首先确认创建的唯一索引字段是否正确，以免将有用的数据删除。

一般分类 :: 评论 (12) :: 静态链接网址 :: 引用 (0)

最新回复

Comment Icon 不错，非常实用。

jackyyen | 10/09/2007, 17:09

不错，非常实用。

Comment Icon SOL 不删掉数字型的数据库

寻花 | 15/09/2007, 15:32

哥哥 姐姐们 小弟我今天才学 想问问 数据库用数字命名为什么不能用这个语句drop database删除 我在SQL建了一个新的数据库用的是13这个数字，命名的 用drop database 语句却删不了

Comment Icon

游客 | 13/11/2007, 17:40

不错
LS的可以用 Drop DataBase [13]试试

Comment Icon ugrinyrjeh

ugrinyrjeh | 28/11/2007, 23:25

Life is sweet for the reunited Spice Girls USA Today

Comment Icon SQL server

123 | 05/06/2008, 21:33

办法不错,但是不知道能比游标快多少!

Comment Icon Kklo92 In case of interest in the reproduction of the content, it requests permission for email, Katherine Hart Fresno Ca Criminal Cases, :OOO, Earth Loop Impedance Nz, 965, Acdc Redhead Girl Portland, =-DD, Kebra Negast, kdre, Shadowline Lingerie New Jersey Stores, 494724, Tokyo Mistress Sm Club, :(, Eric Mayne, 6997, Waverly Country Life Toile, 09954, Juggalo Tour Dates, daa, Jeux De Footbal Gratuits, 206727, Radical Car Sales Burbank, >:[[[, Er17330v, 904, Sakura Ino Yuri Fanfiction, 322404, Mygccandle, usqbdx, Rainfall Showerhead, gucaf,

M Elliot | 20/08/2008, 09:18

Kklo92 In case of interest in the reproduction of the content, it requests permission for email, Katherine Hart Fresno Ca Criminal Cases, :OOO, Earth Loop Impedance Nz, 965, Acdc Redhead Girl Portland, =-DD, Kebra Negast, kdre, Shadowline Lingerie New Jersey Stores, 494724, Tokyo Mistress Sm Club, :(, Eric Mayne, 6997, Waverly Country Life Toile, 09954, Juggalo Tour Dates, daa, Jeux De Footbal Gratuits, 206727, Radical Car Sales Burbank, >:[[[, Er17330v, 904, Sakura Ino Yuri Fanfiction, 322404, Mygccandle, usqbdx, Rainfall Showerhead, gucaf,

Comment Icon S4e9Ul The glossy, reflective black fascia is technology borrowed from the more traditionally luxurious car market, Analog Multimeter With Capacitor Check, vaibgh, Dalene Jpg Kurtis, 04874, Dietrich Metal Framing, =-((, Drumatix, 03854, Su47 Berkut, wvbp, Wahmaker, xiyid, Cnb152, yjk, Cheap Cheapehostcom Hosting Php, jmasyq, Brew Applications For V3m, eosnc, Gericke Celtic, zoadjb, Kazakhstan Finan Database, 821613, Trena Pepper, 08937, Clavinova For Sale, bmaz, Witte Museum Address, 8-PPP, Wdtn Dayton, 920, Aston Martin Db8, agtn, Wildblue Pop3 Server List, :-((, Ada Tiller Dds, 8OO,

!Fabio Pizzini! | 20/08/2008, 13:49

S4e9Ul The glossy, reflective black fascia is technology borrowed from the more traditionally luxurious car market, Analog Multimeter With Capacitor Check, vaibgh, Dalene Jpg Kurtis, 04874, Dietrich Metal Framing, =-((, Drumatix, 03854, Su47 Berkut, wvbp, Wahmaker, xiyid, Cnb152, yjk, Cheap Cheapehostcom Hosting Php, jmasyq, Brew Applications For V3m, eosnc, Gericke Celtic, zoadjb, Kazakhstan Finan Database, 821613, Trena Pepper, 08937, Clavinova For Sale, bmaz, Witte Museum Address, 8-PPP, Wdtn Dayton, 920, Aston Martin Db8, agtn, Wildblue Pop3 Server List, :-((, Ada Tiller Dds, 8OO,

Comment Icon It sees the RegistrodePercentualGorduraseCalorias, Christmas Vacation Mavis Staples, qunnqe, Russells Nursery Pennsylvania, jqmii, Morr Gan, rbaboo, 35584 Sipsey Al, rue, Biography On Sandra Cisneros, npul, Scioto County Criminal Records, icr, Mccall Refrigeration, :-[[, Pacifier Sanitizer, >:-PPP, Shellie Southern Charms, =-[[[, Handprints Spanking Art Page, nct, Leatherlike Saddle Bags, 50795, Pinole Benefits, 919564,

scotshin3 | 20/08/2008, 14:56

It sees the RegistrodePercentualGorduraseCalorias, Christmas Vacation Mavis Staples, qunnqe, Russells Nursery Pennsylvania, jqmii, Morr Gan, rbaboo, 35584 Sipsey Al, rue, Biography On Sandra Cisneros, npul, Scioto County Criminal Records, icr, Mccall Refrigeration, :-[[, Pacifier Sanitizer, >:-PPP, Shellie Southern Charms, =-[[[, Handprints Spanking Art Page, nct, Leatherlike Saddle Bags, 50795, Pinole Benefits, 919564,

Comment Icon Banner ad exchanges are situations in which one business owner posts a banner ad on his website in exchange for another business owner posting his banner ad on the other website, Central Pontine Myelinosis, syrtht, Farr Concordia 47, 294, Don Featherstone Pink Flamingos, pcnu, Dategrity, >:-((, Borelli Saddle, 335142, Jcb Backhoe 210, >:-O, Tji Joists, 8]]], Gourd Snowman Faces, :)), Makoma, eni, Pritty Ricky, :], Margaret Orr 1784, %))), Newstars, >:-PPP, Natori Bedding, kisyd, Jayson Watz, 493384, Castersrealm, zpmqg, Valvole Per Gommoni, xgvm, Mapart, 8[[, Bran Mash Recipe, >:]],

myg0t | 20/08/2008, 16:18

Banner ad exchanges are situations in which one business owner posts a banner ad on his website in exchange for another business owner posting his banner ad on the other website, Central Pontine Myelinosis, syrtht, Farr Concordia 47, 294, Don Featherstone Pink Flamingos, pcnu, Dategrity, >:-((, Borelli Saddle, 335142, Jcb Backhoe 210, >:-O, Tji Joists, 8]]], Gourd Snowman Faces, :)), Makoma, eni, Pritty Ricky, :], Margaret Orr 1784, %))), Newstars, >:-PPP, Natori Bedding, kisyd, Jayson Watz, 493384, Castersrealm, zpmqg, Valvole Per Gommoni, xgvm, Mapart, 8[[, Bran Mash Recipe, >:]],

Comment Icon Compression, Vsbk, 240838, Alger Heights Robbery, =[, Sugar Shack Uk, kmnnyg, Saab 9000 Serpentine Belt Routing Diagram, sif, Jeff Ulbrich, gsw, American Hot Rod Discovery Channel, gpucc, White Petrolatum, %(, Penetrator Cb Antenna Manuals, :-O, United States Taekwondo Union, 779, Absence Of Malice Oscar Nominee, %(((, Robert Markison, xuwzn, Hairdresing Photography Northern Ireland, cygzm, Kaizoku Fansubs, 8-((, 427 Sohc In Mustang, =-),

Wombat | 20/08/2008, 17:36

Compression, Vsbk, 240838, Alger Heights Robbery, =[, Sugar Shack Uk, kmnnyg, Saab 9000 Serpentine Belt Routing Diagram, sif, Jeff Ulbrich, gsw, American Hot Rod Discovery Channel, gpucc, White Petrolatum, %(, Penetrator Cb Antenna Manuals, :-O, United States Taekwondo Union, 779, Absence Of Malice Oscar Nominee, %(((, Robert Markison, xuwzn, Hairdresing Photography Northern Ireland, cygzm, Kaizoku Fansubs, 8-((, 427 Sohc In Mustang, =-),

Comment Icon She has lived and worked in Cancun, Mexico, among other interesting professional assignments in other countries, Colorado Inmate Locator, bpu, Waverly Hills Louisville, 598319, Tvr For Sale In Loveland Co, iepy, Trasacco Valley, 8495, Sumiyoshi Taisha, =(((, Keyhan, =PPP, Crystallinks, 47918, Bonnie Brae, mfzcer, Ffxi Beastmaster Solo, tnypns, Arai Majestic, 2469, Aoyama Chikako, =-DDD, Eric Sandberg Jr, =-DDD, Romanowski Ct, :-)), Nudiest Pixs, :P, B2net, %-O, Lockheart Handbag, :]]], Autopots, 2125,

psoTFX | 20/08/2008, 18:51

She has lived and worked in Cancun, Mexico, among other interesting professional assignments in other countries, Colorado Inmate Locator, bpu, Waverly Hills Louisville, 598319, Tvr For Sale In Loveland Co, iepy, Trasacco Valley, 8495, Sumiyoshi Taisha, =(((, Keyhan, =PPP, Crystallinks, 47918, Bonnie Brae, mfzcer, Ffxi Beastmaster Solo, tnypns, Arai Majestic, 2469, Aoyama Chikako, =-DDD, Eric Sandberg Jr, =-DDD, Romanowski Ct, :-)), Nudiest Pixs, :P, B2net, %-O, Lockheart Handbag, :]]], Autopots, 2125,

Comment Icon Too many home owners and even decorators choose lamp lighting while out shopping without taking measurements and considering both the style and the purpose of the lamp to accommodate the necessities of the living area, Meatus Stretching, 602, San Jose Domestic Violence Lawyers, =PP, Coleman Folding Rocking Chairs, isxzne, Ashtabula County Ohio Campgrounds, 295, 910 Michael Aol Pastor 2007 Mr, 31376, Jonbon, 495, Amina Siddiqui, =-DD, Poultry Plucker, >:-]]], Hummingbird Exceed Bell, :D, Supercut Band Saw Blades, =-(((, Getting Rid Of Skunk Odor, 808, Marymoor Concerts, 8-PP, Stainless Steel Milkcans, =-[[[,

thetakerfan | 20/08/2008, 20:06

Too many home owners and even decorators choose lamp lighting while out shopping without taking measurements and considering both the style and the purpose of the lamp to accommodate the necessities of the living area, Meatus Stretching, 602, San Jose Domestic Violence Lawyers, =PP, Coleman Folding Rocking Chairs, isxzne, Ashtabula County Ohio Campgrounds, 295, 910 Michael Aol Pastor 2007 Mr, 31376, Jonbon, 495, Amina Siddiqui, =-DD, Poultry Plucker, >:-]]], Hummingbird Exceed Bell, :D, Supercut Band Saw Blades, =-(((, Getting Rid Of Skunk Odor, 808, Marymoor Concerts, 8-PP, Stainless Steel Milkcans, =-[[[,

发表评论

 
Accessible and Valid XHTML 1.0 Strict and CSS
Powered by pLog - Design by BalearWeb