DELETE a FROM a ,b WHERE a.成绩 = b.成绩 and a.科目=b.科目
delete a from a,b where a.成绩 = b.成绩 and a.科目=b.科目
delete from a where convert(varchar(10),a.id)+convert(varchar(10),a.成绩)+a.科目 in (select convert(varchar(10),a.id)+convert(varchar(10),a.成绩)+a.科目 from b)
delete from a where (id,成绩,科目) in (select id,成绩,科目 from b)
--比较好一点的写法是这样(任意多个字段) create table a( id int, 成绩 int, 科目 char(8) )select * into b from a where 1=2insert a select 1,45,'英语' union select 2,65,'数学' union select 3,67,'政治' union select 5,90,'体育' union select 4,89,'英语' union select 1,69,'政治'insert bselect 1,45,'英语' union select 2,65,'数学' union select 3,90,'政治' union select 5,90,'体育' union select 4,54,'英语' union select 1,69,'政治' declare @condition varchar(100) set @condition='' select @condition=@condition+'a.'+rtrim(b.name)+'=b.'+rtrim(b.name)+' and ' from sysobjects a,syscolumns b where a.id=b.id and a.xtype='U' and a.name='a'set @condition=left(@condition,len(@condition)-4) set @condition='delete a where exists(select 1 from b where '+@condition+')'exec(@condition)select * from a drop table a drop table b--结果 id 成绩 科目 ----------- ----------- -------- 3 67 政治 4 89 英语 (所影响的行数为 2 行)
delete a where exists (select * from b a.id=b.id)
其實很簡單,我已經測試通過了,不知道上面什麼寫的那麼難?樓主可以試試看delete a from b where a.成绩=b.成绩 and a.科目=b.科目
FROM a ,b WHERE a.成绩 = b.成绩 and a.科目=b.科目
create table a(
id int,
成绩 int,
科目 char(8)
)select * into b from a where 1=2insert a
select 1,45,'英语'
union
select 2,65,'数学'
union
select 3,67,'政治'
union
select 5,90,'体育'
union
select 4,89,'英语'
union
select 1,69,'政治'insert bselect 1,45,'英语'
union
select 2,65,'数学'
union
select 3,90,'政治'
union
select 5,90,'体育'
union
select 4,54,'英语'
union
select 1,69,'政治'
declare @condition varchar(100)
set @condition=''
select @condition=@condition+'a.'+rtrim(b.name)+'=b.'+rtrim(b.name)+' and '
from sysobjects a,syscolumns b where a.id=b.id and a.xtype='U' and a.name='a'set @condition=left(@condition,len(@condition)-4)
set @condition='delete a where exists(select 1 from b where '+@condition+')'exec(@condition)select * from a
drop table a
drop table b--结果
id 成绩 科目
----------- ----------- --------
3 67 政治
4 89 英语 (所影响的行数为 2 行)