删除表中重复记录.doc

1、 数据库中删除重复记录一直是件挺烦人的事，本人收集了 Oracle跟 SqlServer的快速删除重复记录的方法，供大家参考，希望对大家有所帮助。 SQL SERVER 想必每一位 SQL SERVER 开发人员都有过类似的经历，在对数据库进行查询或统计的时候不时地会碰到由于表中存在重复的记录而导致查询和统计结果不准确。解决该问题的办法就是将这些重复的记录删除，只保留其中的一条。 在 SQL Server 中除了对拥有十几条记录的表进行人工删除外，实现删除重复记录一般都是写一段代码，用游标的方法一行一行检查，删除 重复的记录。因为这种方法需要对整个表进行遍历，所以对于表中的记录数不是很大的时候

2、还是可行的，如果一张表的数据达到上百万条，用游标的方法来删除简直是个噩梦，因为它会执行相当长的一段时间。 四板斧 轻松消除重复记录 殊不知在 SQL Server 中有一种更为简单的方法，它不需要用游标，只要写一句简单插入语句就能实现删除重复记录的功能。为了能清楚地表述，我们首先假设存在一个产品信息表 Products，其表结构如下： CREATE TABLE Products ( ProductID int, ProductName nvarchar (40), Unit char(2), UnitPrice money ) 表中的数据如图 1： 图表 图 1中可以看出，产品 Chang 和

3、 Tofu 的记录在产品信息表中存在重复。现在要删除这些重复的记录，只保留其中的一条。 步骤如下： 第一板斧 建立一张具有相同结构的临时表 CREATE TABLE Products_temp ( ProductID int, ProductName nvarchar (40), Unit char(2), UnitPrice money ) 第二板斧 为该表加上索引，并使其忽略重复的值 方法是在企业管理器中找到上面建立的临时表 Products _temp，单击鼠标右键，选择所有任务，选择管理索引，选择新建。如图 2 所示。 按照图 2中圈出来的地方设置索引选项。 图 2 第三板斧 拷贝产品

4、信息到临时表 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_

5、temp drop table Products_temp 这样就完成了对表中重复记录的删除。无论表有多大，它 的执行速度都是相当快的，而且因为几乎不用写语句，所以它也是很安全的。 小提示 ：上述方法中删除重复记录取决于创建唯一索引时选择的字段，在实际的操作过程中读者务必首先确认创建的唯一索引字段是否正确，以免将有用的数据删除。 ORACLE 在 Oracle 中，可以通过唯一 rowid 实现删除重复记录；还可以建临时表来实现 .这个只提到其中的几种简单实用的方法，希望可以和大家分享（以表 employee 为例）。 SQL desc employee Name Null? Type emp

6、_id NUMBER(10) emp_name VARCHAR2(20) salary NUMBER(10,2) 可以通过下面的语句查询重复的记录： SQL select * from employee; EMP_ID EMP_NAME SALARY 1 sunshine 10000 1 sunshine 10000 2 semon 20000 2 semon 20000 3 xyz 30000 2 semon 20000 SQL select distinct * from employee; EMP_ID EMP_NAME SALARY 1 sunshine 10000 2 semon 2

7、0000 3 xyz 30000 SQL select * from employee group by emp_id,emp_name,salary having count (*)1 EMP_ID EMP_NAME SALARY 1 sunshine 10000 2 semon 20000 SQL select * from employee e1 where rowid in (select max(rowid) from employe e2 where e1.emp_id=e2.emp_id and e1.emp_name=e2.emp_name and e1.salary=e2.s

8、alary); EMP_ID EMP_NAME SALARY 1 sunshine 10000 3 xyz 30000 2 semon 20000 2. 删除的几种方法 ： （ 1）通过建立临时表来实现 SQLcreate table temp_emp as (select distinct * from employee) SQL truncate table employee; (清空 employee 表的数据） SQL insert into employee select * from temp_emp; (再将临时表里的内容插回来） ( 2）通过唯一 rowid 实现删除重复记录

9、.在 Oracle 中，每一条记录都有 一个 rowid， rowid 在整个数据库中是唯一的， rowid 确定了每条记录是在 Oracle中的哪一个数据文件、块、行上。在重复的记录中，可能所有列的内容都相同，但 rowid 不会相同，所以只要确定出重复记录中那些具有最大或最小 rowid 的就可以了，其余全部删除。 SQLdelete from employee e2 where rowid not in ( select max(e1.rowid) from employee e1 where e1.emp_id=e2.emp_id and e1.emp_name=e2.emp_name

10、 and e1.salary=e2.salary);-这里用 min(rowid)也可以。 SQLdelete from employee e2 where rowid delete from employee where rowid not in ( select max(t1.rowid) from employee t1 group by t1.emp_id,t1.emp_name,t1.salary);-这里用 min(rowid)也可以。 EMP_ID EMP_NAME SALARY 1 sunshine 10000 3 xyz 30000 2 semon 20000 answer5

11、05 发表于 :2005.10.26 15:24 :分类 : ( 一般分类 ) :阅读 :(114 次 ) : 评论 (0) : 引用 (0) 从 Oracle 迁移到 SQL Server 的陷阱 在把 Oracle查询转换为 SQL Server的时候要特别当心一些不容易注意到的问题。我们知道， T-SQL 是 SQL Server 的语言引擎，而 Oracle 的语言引擎却是PLSQL。这两种查询语言都对 ANSI SQL-92 标准进行了扩展以提供额外的支持力度。你所创建的应用程序几乎都要用到这些补充特性。本文就对最常用的、非标准的 Oracle 扩展进行了说明，同时还要 介绍下如何对

12、这些扩展进行转化以用在SQL Server 环境下。 列的选择 用 PLSQL 执行数据查询的时候， FROM 子句是必须的，这同 SQL Server 的要求是一样的。 SELECT 语句必须选择针对的数据表。在 Oracle 数据库内有一种特殊的表 DUAL。 DUAL 表由 Oracle 连同数据字典一同创建，所有的用户都可以用名称 DUAL 访问该表。这个表里只有一列 DUMMY，该列定义为 VARCHAR2(1)类型，有一行值 X。 从 DUAL 表选择数据常被用来通过 SELECT 语句计算常数表达式，由于 DUAL只 有一行数据，所以常数只返回一次。 Oracle 下的 DUAL

13、 查询如下所示： SELECT x FROM dual 而对等的 SQL Server 查询则是下面这个样子： SELECT x 连接 Oracle 用 | 符号作为连接符，而 SQL Server 的连接符是加号： + 。 Oracle 查询如下所示： Select Name | Last Name From tableName 对应的 SQL Server 查询如下所示： Select Name + Last Name 数字取舍 Oracle 数据库内有一个 TRUNC 函数，该函数返回 m位十进制数的 n 位；如果省略 m则 n就是 0位。 m 的值可以为负，表示截去小数点左边 m位数字

14、。 在 SQL Server 下可以用 Round 或者 Floor。 以下是 Oracle 查询： SELECT TRUNC(15.79,1) “Truncate“ FROM DUAL; 下面是同类查询的 SQL Server 版本： SELECT ROUND(15.79, 0) rounded , ROUND(15.79, 0,1) truncated SELECT FLOOR(ROUND(15.79, 0), FLOOR(ROUND(15.79, 0,1) ) 数字转换 Oracle 的 TO_CHAR 函数可以把 n 位 NUMBER 数据类型转换为 VARCHAR2 数据类型，同时采

15、用可选的数字格式。 SQL Server 则通过 STR 函数返回数字转换之后的字符数据。不过，该函数不具方便的 Format 参数。 Oracle 查询如下： SELECT to_char(123.45 ,99999999999999) from tab SELECT to_char(EXPIRY_DATE,DDMONYYYY) from tab 以下是 SQL Server 版本的查询： SELECT STR(123.45, 14) SELECT STR(round(123.455 , 2),12,2) SELECT CAST(REPLACE(CONVERT(varchar(12) , E

16、XPIRYDATE, 106 ), , ) as varchar(9) LENGTH 和 LEN 以下是 Oracle 的查询： SELECT LENGTH(SQLMAG) “Length in characters“ FROM DUAL; 以上查询在 SQL Server 下是这样写的： SELECT LEN(SQLMAG) “Length in characters“ 日期 以上两种系统都具有各自的当前日期和时间格式。 Oracle 取得日期和采用如下方式： SYSDATE SQL Server 则是这样的： GETDATE() 你可以用各种语法操 作日期。以下的代码对 Oracle 日期

17、值中的月份进行了格式调整（返回日期再加上 n 月）： Select add_months(sysdate,12) from dual SQL Server 则是如下完成同等功能的： Select dateadd(mm,12,getdate() 数据的减法也不同。以下代码在 Oracle 中直接对数据进行减法操作： SELECT sysdate -add_months(sysdate,12) FROM dual SQL Server 则是这样做的： SELECT datediff(dd, GetDate(),dateadd(mm,12,getdate() 小结 幸而，我在从 Oracle 迁移到

18、 SQL Server 2000 的过程中并没有遭遇太棘手的问题。当然，一开始我就深知系统之间肯定存在显著的差异。我希望本文列出的差别有助于你避免一些常见的问题。 answer505 发表于 :2005.10.26 15:23 :分类 : ( 一般分类 ) :阅读 :(94 次 ) : 评论 (0) : 引用 (0) 利用 MSSQL sp 自制未公开的加密函数 如果对 MSSQL 的用户信息有兴趣的，可能会发现 master.dbo.sysxlogins里面存放着用户的口令，可是呢， password 字段如果不是 null 就是一堆看不懂的 binary，这个口令是怎么加密的呢？ 其实只要

19、仔细看看 master.dbo.sp_addlogin 就知道了， MSSQL 的 sp 都可以看到代码，真是不错。 让我们来看看它是怎么做的，注意这一行 select passwd = pwdencrypt(passwd)，这个时后 passwd 就被加密了，让我们也来试一下 DECLARE ClearPWD varchar(255) DECLARE EncryptedPWD varbinary(255) SELECT ClearPWD = test SELECT EncryptedPWD = CONVERT(varbinary(255), pwdencrypt(ClearPWD) SELE

20、CT EncryptedPWD 看上去不错，确实被加密了，可是我怎么还原呢？ 呵呵，这就没戏了，口令加密都是单向的，用加密后的密文来比较就可以了。 继续看看其它用户相关的 sp， 可以发现 master.dbo.sp_password 里面有口令比较的内容。 pwdcompare(old, password, (CASE WHEN xstatus&2048 = 2048 THEN 1 ELSE 0 END) 不用去理会 xstatus，这是一个状态掩码，一般我们用的时候就直接用 0就可以了 DECLARE ClearPWD varchar(255) DECLARE EncryptedPWD v

21、arbinary(255) SELECT ClearPWD = test SELECT EncryptedPWD = CONVERT(varbinary(255), pwdencrypt(ClearPWD) SELECT pwdcompare(ClearPWD, EncryptedPWD, 0) SELECT pwdcompare(ErrorPassword, EncryptedPWD, 0) 这样我们就可以使用这两个函数来加密自己的密码了 :) 。 answer505 发表于 :2005.10.26 15:16 :分类 : ( 一般分类 ) :阅读 :(111 次 ) : 评论 (0) :

22、引用 (0) 存储过程编写经验和优化措施 一、前言： 在经过一段时间的存储过程开发之后，写下了一些开发时候的小结和经验与大家共享，希望对大家有益，主要是针对 Sybase 和 SQL Server 数据库，但其它数据库应该有一些共性。 二、适合读者对象： 数据库开发程序员，数据库的数据量很多，涉及到对 SP（存储过程）的优化的项目开发人员，对数据库有浓厚兴趣的人。 三、介绍： 在数据库的开发过程中，经常会遇到复杂的业务逻辑和对数据库的操作，这个时候就会用 SP 来封装数据库操作。如果项目的 SP 较多，书写又没有一定的规范，将会影响以后的系统维护困难和大 SP 逻辑的难以理解，另外如果数据库的

23、数据量大或者项目对 SP 的性能要求很，就会遇到优化的问题，否则速度有可能很慢，经过亲身经验，一个经过优化过的 SP要比一个性能差的 SP 的效率甚至高几百倍。 四、 内容： 1、开发人员如果用到其他库的 Table 或 View，务必在当前库中建立 View来实现跨库操作，最好不要直接使用 “databse.dbo.table_name” ，因为sp_depends 不能显示出该 SP所使用的跨库 table 或 view，不方便校验。 2、开发人员在提交 SP 前，必须已经使用 set showplan on 分析过查询计划，做过自身的查询优化检查。 3、高程序运行效率，优化应用程序，在 SP编写过程中应该注意以下几点： a) SQL 的使用规范： i. 尽量避免大事务操作，慎用 holdlock 子句，提高系统并发能力。