问题:一个表有自增的ID列,表中有一些记录内容重复,也就是说这些记录除了ID不同之外,其他的信息都相同。需要把重复的记录保留一条,剩下的删除。
这种需求一般开发人员都会,我这里写出两个版本。
版本一:由于记录有自增列,所以自增列可以做为记录的唯一标识,由此可见,重复的记录的自增ID是一个递增关系,这里我们可以只保留ID最小的那条记录,其它的全部删除。利用一个嵌套语句就非常容易写出下面的SQL。其中的sname,saddress是记录除了ID外的所有列。
DELETE FROM a
WHERE id NOT IN ( SELECT MIN(id)
FROM a
GROUP BY sname,
saddress )
版本二:充分利用SQL05的几个比较实用的特性。这里先简单说说要用到的几个特性。详细用法可到网上搜索下。
1:ROW_NUMBER,它的作用就是用来生成行号,默认是从1开始。
2:公用表表达式(CTE),我这里并不会利用它的递归,而是用它来简化嵌套查询及对表自身引用功能。CTE的语法如下:
[ WITH <common_table_expression> [ ,n ] ]
<common_table_expression>::=
expression_name [ ( column_name [ ,n ] ) ]
AS
( CTE_query_definition )
说明:1>CTE在某种程序上相当表变量或者临时表的功能。但比起表变量来说它最大的优势是对自身的引用,CTE语句后面紧跟的select ,update,delete等,操作的结果都会直接反应的实际物理表中。相比临时表,最大优势无非是性能,临时表实际是一张物理存在的表,在对它进行操作时,会产生额外的IO开销以及管理上的开销。
2>CTE语法后面需要直接跟上使用CTE的相关语句select ,update,delete等,否则CTE会失效,下面的语句是错误的:
WITH b AS ( SELECT ROW_NUMBER() OVER ( PARTITION BY sname, saddress ORDER BY sname, saddress ) AS rn,
*
FROM a
)
DELETE FROM b
WHERE rn > 1
SELECT * from a
SELECT * FROM b WHERE rn>1
3:PARTITION BY,分区函数。和聚合函数不同的地方在于它能返回一个分组中的多条记录,聚合函数一般只有一条反映统计值的记录,partition by用于给结果集分组,如果没有指定那么它把整个结果集作为一个分组 。
经过上面的三个关键字的介绍后,下面给出三者相结合后的结果。
WITH b AS ( SELECT ROW_NUMBER() OVER ( PARTITION BY sname, saddress ORDER BY sname, saddress ) AS rn,
*
FROM a
)
DELETE FROM b
WHERE rn > 1
版本一和版本二比较:
1:版本二更加容易阅读。
2:版本二性能较版本一强。我们可以通过以以信息来看。可以看到版本一会发生两次表扫描。
Table 'a'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(0 row(s) affected)
Table 'a'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(0 row(s) affected)
分享到:
相关推荐
SQL Server 2005中的Transact-SQL(T-SQL)是其主要的编程语言,用于查询、更新、插入和删除数据,以及创建和管理数据库对象。学习者应掌握T-SQL的基础语法,包括SELECT、INSERT、UPDATE、DELETE语句,以及如何创建...
在SQL Server 2005中,除了基本的SQL操作,还有许多高级特性,如视图、存储过程、触发器、索引等。视图可以提供虚拟表,方便数据的汇总和安全性控制;存储过程是预编译的SQL语句集合,能提高执行效率并减少网络流量...
总的来说,SQL Server 2005 SQL JDBC_4.0驱动是Java开发者连接和操作SQL Server数据库的重要工具,它提供了稳定、高效且功能丰富的接口,使Java应用程序能够充分利用SQL Server的功能。通过熟练掌握这个驱动,开发者...
以上只是SQL Server 2008新特性的一部分,通过学习本课程,开发者可以掌握这些新功能,更好地利用SQL Server 2008进行高效的数据应用开发。提供的资源包括PPT、视频、文档和示例,全方位覆盖了学习和实践的需求。
事务处理和并发控制也是SQL Server 2005中的重要知识点。了解事务的基本特性(原子性、一致性、隔离性和持久性),掌握锁机制和事务隔离级别,这对于保证数据库的一致性和完整性至关重要。 最后,SQL Server 2005还...
### 商务智能与SQL Server 2005 BI平台 #### 一、商务智能概述 商务智能(Business Intelligence,简称BI)是一种集成了多种技术、工具和方法的综合性解决方案,旨在帮助企业更好地理解和利用其内部及外部的数据...
在SQL Server中,数据库脚本是数据库管理员和开发人员日常工作中不可或缺的一...通过实践和不断地学习,你可以逐步掌握更高级的特性和技巧,如事务处理、触发器、分区函数等,从而更好地利用SQL Server进行数据管理。
5. **ROW_NUMBER()函数**:在支持窗口函数的数据库系统(如SQL Server, PostgreSQL)中,可以利用`ROW_NUMBER()`生成行号,结合`PARTITION BY`和`ORDER BY`来标记重复行,然后删除非首行。例如: ```sql WITH ...
3. **事务与并发控制**:SQL Server支持ACID(原子性、一致性、隔离性和持久性)特性,保证了数据的一致性。了解事务的隔离级别(读未提交、读已提交、可重复读、串行化)以及锁的概念,对于多用户环境下的数据库...
通过本教程的学习,你可以从零开始,逐步掌握SQL Server 2005的基本操作和高级功能,为实际工作中的数据管理打下坚实的基础。实践是最好的老师,建议在学习过程中结合实际案例进行操作练习,以加深理解和应用能力。
在SQL Server 2005中,动态表无限级分类是一种常见的数据建模技术,用于构建具有层级关系的数据结构,例如组织结构、产品目录或菜单系统。这些层级关系可以通过自引用的方式实现,其中每个记录都有一个父记录的引用...
作为微软的重要产品之一,SQL Server 2008 在企业级应用中占据了非常重要的位置,广泛应用于各种规模的企业中,为企业提供了强大的数据支持。 #### 二、SQL Server 2008 特性 1. **增强的安全性**:SQL Server ...
本教程涵盖了SQL Server的核心概念和技术,包括基础语法、数据操作以及高级特性,通过理论与实践相结合的方式,使学习者能够扎实地掌握SQL Server的应用。 在SQL函数部分,你将学习到如何使用内置的算术、字符串、...
`建立分页Table.sql`, `2005分页.sql`可能讲解了SQL Server 2005中实现数据分页的新方法。在大型数据集上,分页查询可以提高性能并提供更好的用户体验。SQL Server 2005引入了ROW_NUMBER()函数,配合TOP和...
在IT行业中,数据库是存储和管理数据的核心工具,而SQL Server作为一款强大的关系型数据库管理系统,广泛应用于企业级数据管理。本主题将深入探讨“SQL Server数据库操作类”,特别是通过C#编程语言实现的`DbHelper...
综上所述,MyBatis与SQL Server的整合使用,不仅能让开发者充分利用SQL Server的特性,还能借助MyBatis的自动代码生成工具提升开发效率。理解并掌握这一整合,对于提升开发者的生产力具有重要意义。
当一个表引用另一个表的主键作为外键时,SQL Server可以设置FOREIGN KEY约束,从而防止插入不存在的引用值或删除被引用的主键记录。 4. 用户定义完整性:这是用户根据业务规则自定义的完整性约束,可以涵盖以上三种...
1. **PowerBuilder集成**:框架集成了对PowerBuilder的支持,使得PB开发者能够更方便地利用Sqlserver数据库进行数据操作。PB的图形化界面和强大的数据窗口组件与框架相结合,能够快速构建数据库应用。 2. **通用...
SQL Server的存储过程是数据库管理系统中的一个重要特性,它是一组预先定义并编译好的SQL语句,用于执行特定的任务。存储过程的使用极大地提升了数据库应用的效率和安全性。以下是关于SQL Server存储过程的详细说明...
本书的目的是帮助读者熟悉其核心概念,掌握SQL Server 2000的编程语言T-SQL(Transact-SQL),以及如何利用SQL Server 2000提供的各种功能进行高效的数据处理。 首先,本书将详细介绍SQL Server 2000的基础知识,...