在有些特殊的情况下,我们需要对库中一下重复的数据删除,于是有了我的苦思冥想,也没搞清楚,于是乎,请教高人指点,加以整合。

数据表实例:

Id 为自动增长列
Id      Name    Age     Createtime
43      张三    13     2013-04-12 12:33:33.000
44      张三    13     2013-04-12 12:43:33.000
45      赵六    13     2013-04-12 12:33:33.000
46      李四    13     2013-04-12 12:31:33.000
47      李四    13     2013-04-12 12:31:33.000
48      王五    13     2013-04-12 12:00:33.000
49      王五    13     2013-04-12 12:00:33.000
50      王五    13     2013-04-12 12:00:33.000

问题如下:
1.查询表中每个姓名的最后记录

select name,max(createtime)as time from t_user group by name

2. 删除表中记录完全相同的数据(保留最近记录)
  删除重复数据sql思路:
  ①查询出有重复数据中最近记录的数据
  select max(id) id,name,age,createtime from t_user group by name,age,createtime having count(*)>1
  ②查询出除最近记录的其他数据
select a.* from t_user a,(select max(id) id,name,age,createtime from t_user group by name,age,createtime having count(*)>1) b where a.name=b.name and a.age=b.age and a.createtime=b.createtime and a.id<>b.id
  ③删除除最近记录的其他数据
delete a from t_user a, (select max(id) id,name,age,createtime from t_user  group by name,age,createtime having count(*)>1) b where a.name=b.name and a.age=b.age and a.createtime=b.createtime and a.id<>b.id

sql查询总结
 查询name,age,createtime 三个字段都相同的重复数据,
select name,age,createtime from t_user
 group by name,age,createtime having count(*)>1

 查询字段重复的数据
 selete A,B... from TableA group by A,B... having count(*)>1

------------用到的数据库-----------------

CREATE DATABASE Test;
go
USE Test

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t_user]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[t_user]
GO

CREATE TABLE [dbo].[t_user] (
 [Id] [int] IDENTITY (1, 1) NOT NULL ,
 [Name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [Age] [int] NULL ,
 [Createtime] [datetime] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[t_user] ADD
 CONSTRAINT [DF_t_user_Createtime] DEFAULT (getdate()) FOR [Createtime]
GO

 

insert into t_user(name,age,createtime)values('张三',13,'2013-04-12 12:33:33')
insert into t_user(name,age,createtime)values('张三',13,'2013-04-12 12:43:33')
insert into t_user(name,age,createtime)values('赵六',13,'2013-04-12 12:33:33')
insert into t_user(name,age,createtime)values('李四',13,'2013-04-12 12:31:33')
insert into t_user(name,age,createtime)values('李四',13,'2013-04-12 12:31:33')
insert into t_user(name,age,createtime)values('王五',13,'2013-04-12 12:00:33')
insert into t_user(name,age,createtime)values('王五',13,'2013-04-12 12:00:33')
insert into t_user(name,age,createtime)values('王五',13,'2013-04-12 12:00:33')