id name age
1 jack 20
2 rose 30
3 jack 20
4 book 100
5 bady 30
6 rose 30如上面这个 ,我需要删除其他重复的字段!!!! 根据ID删除 大虾们帮帮忙啊。
1 jack 20
2 rose 30
3 jack 20
4 book 100
5 bady 30
6 rose 30如上面这个 ,我需要删除其他重复的字段!!!! 根据ID删除 大虾们帮帮忙啊。
解决方案 »
- 如何将select出来的内容定时发邮件
- 请教Like的一种用法?
- 怎样插入时间
- 执行下面的语句后, 提示影响的行数为7 行, 但是我用企业管理器看过数据类型还是原来的char型, 没有做转换
- 求教个sql语句
- SQL2000存储过程每运行一段时间就必须重新编译否则很慢是为什么?
- sql server传输数据的时候,如何保证数据的安全?
- 使用数据库程序的客户端上怎么实现用程序安装SQL数据源?
- 如何释放SQL server表所占用的空间
- 关于缺省值及类型的问题?(入门者的热盼望,在线等待,给分80)
- 求SQL中函数的使用方法?
- SQL2000数据库事务日志LDF文件大小是数据文件MDF的10几倍,对数据库的数据存取是否有影响?
delete from t where id not in (select min(id) from t group by name,age)
from tb t
where exists(select 1
from tb
where name=t.name and age=t.age and id>t.id)
drop table tb
Go
Create table tb([id] int,[name] nvarchar(4),[age] int)
Insert tb
select 1,N'jack',20 union all
select 2,N'rose',30 union all
select 3,N'jack',20 union all
select 4,N'book',100 union all
select 5,N'bady',30 union all
select 6,N'rose',30
Go
delete t
from tb t
where exists(select 1
from tb
where name=t.name and age=t.age and id>t.id)
select *
from tb
/*
id name age
----------- ---- -----------
3 jack 20
4 book 100
5 bady 30
6 rose 30(4 個資料列受到影響)
*/
where id>(select min(id)
from tb
where t.name=name and age=t.age )
drop table tb
Go
Create table tb([id] int,[name] nvarchar(4),[age] int)
Insert tb
select 1,N'jack',20 union all
select 2,N'rose',30 union all
select 3,N'jack',20 union all
select 4,N'book',100 union all
select 5,N'bady',30 union all
select 6,N'rose',30
Godelete b from tb b
where exists
(select * from tb where [name]=b.[name] and [age]=b.[age] and [id]<b.[id])select * from tbid name age
----------- ---- -----------
1 jack 20
2 rose 30
4 book 100
5 bady 30(4 行受影响)
where id>(select min(id)
from tb
where t.name=name and age=t.age )UP 这个没问题的!
delete t from tb t
where id><(select min(id)from tb
where t.name=name and age=t.age )
Insert tb
select 1,'jack',20 union all
select 2,'rose',30 union all
select 3,'jack',20 union all
select 4,'book',100 union all
select 5,'bady',30 union all
select 6,'rose',30select * from tbdelete from tb where id not in(select max(id) from tb group by name)
如果用not in 会让人哭的,原因不说了。
DELETE tb FROM tb t WHERE EXISTS (SELECT 1 FROM tb WHERE NAME =t.name AND age=t.age AND id>t.id)
http://topic.csdn.net/u/20080626/00/43d0d10c-28f1-418d-a05b-663880da278a.html
go
create table #test
(
id int,
name varchar(100),
age int
)
goinsert into #test values(1 ,'jack', 20)
insert into #test values(2 ,'rose', 30)
insert into #test values(3 ,'jack', 20)
insert into #test values(4 ,'book', 100)
insert into #test values(5 ,'bady', 30)
insert into #test values(6 ,'rose', 30)
goselect * from #test
godelete from #test
where id not in
(
select max(id) from #test
group by name, age
)
go
select * from #test
go3 jack 20
4 book 100
5 bady 30
6 rose 30
if object_id('tb') is not null drop table tb
go
create table tb
(
id int,
[name] varchar(25),
age int
)
insert tb
select 1,'jack',20 union all
select 2,'rose',30 union all
select 3,'jack',20 union all
select 4,'book',100 union all
select 5,'bady',30 union all
select 6,'rose',30
go
begin tran
delete t from
[tb] t
where id <> (select min(id) from tb where t.[name]=[name] and t.age=age)select * from tb
rollback tran