delete t from tb t,(select [name] from tb group by [name] having count(*)>1)b where t.[name]=b.[name] and t.age>60
select name,age from user group by name,age order by age ascdelete user where age>60
Delete t1 where (select [name] from t1 left join (select [name] from t1 group by name having count(name)>1)) and age>60适合当Name and Age 删除所有的,当两行name 和age 完全相同,如果要删除一行,而保留一行时,就加一个Row_Number()来删除,方法大至差不多!
delete a from tb a,(select [name] from tb t where exists(select 1 from tb where name=t.name and age<>t.age)b where a.[name]=b.[name] and a.age>60
--> 测试数据: @table declare @table table (name varchar(1),age int) insert into @table select 'a',23 union all select 'a',24 union all select 'a',25 union all select 'b',59 union all select 'b',60 union all select 'b',61 union all select 'c',60 union all select 'c',61 union all select 'c',62 union all select 'd',67 union all select 'h',56SELECT a.* FROM @table a LEFT JOIN ( select name from @table GROUP BY name HAVING(COUNT(*)>1))b ON a.name=b.name WHERE b.name IS NOT null ORDER BY a.age /* name age ---- ----------- a 23 a 24 a 25 b 59 b 60 c 60 c 61 b 61 c 62 */你想要什么样的结果?d 67岁 只有一个 删吗?
--查询表AUser中Name相同的数据列出来 按Age排序。 select * from AUser where name in (select name from AUser group by name having count(1) > 1 ) order by age--删除AGE > 60 delete from AUser where AGE > 60
delete from AUser t where name in (select name from AUser group by name having count(1) > 1 ) and age > 60
from tb t,(select [name] from tb group by [name] having count(*)>1)b
where t.[name]=b.[name]
and t.age>60
a
from
tb a,(select [name] from tb t where exists(select 1 from tb where name=t.name and age<>t.age)b
where
a.[name]=b.[name]
and
a.age>60
--> 测试数据: @table
declare @table table (name varchar(1),age int)
insert into @table
select 'a',23 union all
select 'a',24 union all
select 'a',25 union all
select 'b',59 union all
select 'b',60 union all
select 'b',61 union all
select 'c',60 union all
select 'c',61 union all
select 'c',62 union all
select 'd',67 union all
select 'h',56SELECT a.* FROM @table a
LEFT JOIN (
select name from @table GROUP BY name HAVING(COUNT(*)>1))b
ON a.name=b.name
WHERE b.name IS NOT null
ORDER BY a.age
/*
name age
---- -----------
a 23
a 24
a 25
b 59
b 60
c 60
c 61
b 61
c 62
*/你想要什么样的结果?d 67岁 只有一个 删吗?
select * from AUser where name in (select name from AUser group by name having count(1) > 1 ) order by age--删除AGE > 60
delete from AUser where AGE > 60
delete from AUser t where name in (select name from AUser group by name having count(1) > 1 ) and age > 60