delete from tablename A
where [type]='T'
and exists ( select 1
from tablename
where [name]=A.name
and rq=A.rq
and [type]='B'
)
where [type]='T'
and exists ( select 1
from tablename
where [name]=A.name
and rq=A.rq
and [type]='B'
)
庄永忠 A
庄永忠 B
张明达 A
叶武龙 A
叶武龙 B
吴雄 B
林文 A
林文 B
要得到的结果是
TableB庄永忠 B
张明达 A
叶武龙 B
吴雄 B
林文 B要求每个人名字在表tableB内只能出现一次,tableA 中名字只出现一次的人的记录不准删除
当出现大于1次时,删除type为A的记录,这回我终于写清楚了,老大帮忙呀~~~~
--??-- 楼上的语法~``有点小问题,看看如下的:DELETE a FROM 表 a
WHERE [type]='T'
AND EXISTS (SELECT 1
FROM 表
WHERE name = a.name
AND [type]='B'
)
WHERE [type]='A'
AND EXISTS (SELECT 1
FROM tableA
WHERE name = a.name
AND [type]='B'
)
from tableA a
where (select count(*) from tablea where name=a.name)=2
and type='A'
declare @tableA table(name varchar(10),type varchar(1))
insert into @tableA
select '庄永忠','A'
union all select '庄永忠','B'
union all select '张明达','A'
union all select '叶武龙','A'
union all select '叶武龙','B'
union all select '吴雄','B'
union all select '林文','A'
union all select '林文','B'--删除处理
delete from @tableA
from @tableA a
where (select count(*) from @tablea where name=a.name)=2
and type='A'--显示处理结果
select * from @tableA/*--测试结果
name type
---------- ----
庄永忠 B
张明达 A
叶武龙 B
吴雄 B
林文 B(所影响的行数为 5 行)
--*/
from tableA a
where (select count(*) from tablea where name=a.name)>=2
and type='A'
庄永忠 1 100501 生产课 2003-12-27 T 8 422
庄永忠 1 100501 生产课 2003-12-27 T 8 600
-------如果不会出现如上的数据记录的话,可以用很简单的方法解决 ---------
庄永忠 1 100501 生产课 2003-12-27 B 8 656
张明达 10 10020101 冷煅组 2003-12-27 T 8 423
叶武龙 102 10050101 冲压组 2003-12-27 T 8 425删除语句为:
delete from a
where (select count(name) from a )>1 and type='T'
select name into #TmpB from TBBX200312 where lb='B'
select a.name into #TmpA from #TmpT a, #TmpB b where a.name=b.name
delete from TBBX200312 a, #TmpA b where a.name=b.namet and a.lb='T'
delete a from TBBX200312 a,#TmpName b where a.name=b.name and type='T' and rq=''