发布时间:2024-09-25 11:00:58 来自:sunnyfish
在有些特殊的情况下,我们需要对库中一下重复的数据删除,于是有了我的苦思冥想,也没搞清楚,于是乎,请教高人指点,加以整合。
数据表实例:
Id 为自动增长列
Id
43
44
45
46
47
48
49
50
问题如下:
1.查询表中每个姓名的最后记录
select name,max(createtime)as time from t_user group by name
2. 删除表中记录完全相同的数据(保留最近记录)
select a.* from t_user a,(select max(id) id,name,age,createtime
from t_user
delete a from t_user a, (select max(id) id,name,age,createtime from
t_user
sql查询总结
select name,age,createtime from t_user
------------用到的数据库-----------------
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] (
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[t_user] ADD
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')