有下述表：
num str_0 str_1 Type
1 a aa fuzzy
3 b bb fuzzy
8 c c equal
19 a a equal
78 d dd fuzzy
34 b b equal
建表：
create table #tb(num int, str_0 varchar(10),str_1 varchar(10),[Type] varchar(10))
insert into #tb values(1,'a','aa','fuzzy')
insert into #tb values(3,'b','bb','fuzzy')
insert into #tb values(8,'c','c','equal')
insert into #tb values(19,'a','a','equal')
insert into #tb values(78,'d','dd','fuzzy')
insert into #tb values(34,'b','b','equal')
问题：
对于同一个str_0，如果有Type=equal存在，就删除Type=fuzzy所在的行，即想要的结果：
num str_0 str_1 Type
8 c c equal
19 a a equal
78 d dd fuzzy
34 b b equal
------解决方案--------------------
delete from #tb where [type]='fuzzy' and str_0 in(select distinct str_0 from #tb where [type]='equal')
------解决方案--------------------
- SQL code
delete from #tb where exists(select 1 from #tb B where str_0 = b.str_0 and type='equal') and TYPE='fuzzy'/*8 c c equal19 a aa equal34 b bb equal*/
------解决方案--------------------
上面错了：
- SQL code
create table #tb(num int, str_0 varchar(10),str_1 varchar(10),[Type] varchar(10))insert into #tb values(1,'a','aa','fuzzy')insert into #tb values(3,'b','bb','fuzzy')insert into #tb values(8,'c','c','equal')insert into #tb values(19,'a','a','equal')insert into #tb values(78,'d','dd','fuzzy')insert into #tb values(34,'b','b','equal')select * into #afrom #tb a where a.Type='fuzzy' and exists(select 1 from #tb b where a.str_0 =b.str_0 and type='equal' )delete from #tb where num in (select num from #a)--执行结果(2 行受影响)select * from #tb--查询结果：num str_0 str_1 Type----------- ---------- ---------- ----------8 c c equal19 a a equal78 d dd fuzzy34 b b equal(4 行受影响)