原表:
name condition price
a aa 1
a aab 1
b d 1
b c 2
c c 1结果
name condition price
a aa 1
b d 1
b c 2
c c 1 条件是name 和price=1 作为条件的记录要是不唯一,删除一个。怎么做,谢谢大家!
因为数据太多了,我现在不能做unic约束,想通过代码!
name condition price
a aa 1
a aab 1
b d 1
b c 2
c c 1结果
name condition price
a aa 1
b d 1
b c 2
c c 1 条件是name 和price=1 作为条件的记录要是不唯一,删除一个。怎么做,谢谢大家!
因为数据太多了,我现在不能做unic约束,想通过代码!
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([name] varchar(1),[condition] varchar(3),[price] int)
insert [TB]
select 'a','aa',1 union all
select 'a','aab',1 union all
select 'b','d',1 union all
select 'b','c',2 union all
select 'c','c',1select * from [TB] T where not exists(select 1 from TB where t.name=name and t.price=price and t.condition>condition)
/*
name condition price
---- --------- -----------
a aa 1
b d 1
b c 2
c c 1(所影响的行数为 4 行)*/drop table TB
alter table tb
add id int identity(1,1)
然后delete t
from tb t
where exists
(
select 1 from tb where t.name=name and t.price=price and t.id>id
)然后
alter table tb
drop column id
8> go(1 rows affected)
name condition price
---- ---------- -----------
a aa 1
a aab 1
b d 1
b c 2
c c 1(5 rows affected)
1>
1> delete a from t_deven1116 a
2> where exists (select 1 from t_deven1116 where [name]=a.name and price=a.price
and condition<a.condition);
3> go(1 rows affected)
1> select * from t_deven1116;
2> go
name condition price
---- ---------- -----------
a aa 1
b d 1
b c 2
c c 1(4 rows affected)
1>
where exists (select 1 from t_deven1116 where [name]=a.name and price=a.price and condition<a.condition);
DELETE [ FROM ] table_name [ WHERE < search_condition > ]
declare @tb table (name varchar(1),condition varchar(3),price int)
insert into @tb
select 'a','aa',1 union all
select 'a','aab',1 union all
select 'b','d',1 union all
select 'b','c',2 union all
select 'c','c',1delete a
from @tb a
where exists(select 1 from @tb where name=a.name and price=1 and condition<a.condition )
and price=1
select * from @tbname condition price
---- --------- -----------
a aa 1
b d 1
b c 2
c c 1(4 行受影响)
TB
FROM TB T
WHERE EXISTS(SELECT 1 FROM TB WHERE NAME=T.NAME AND T.CONDITION>CONDITION)