例如:sno name sd
1041 赵国庆 通信
1041 赵国庆 会计
当1041号没有学过“计算机”、“网络”课程的,那么就插入2条记录
sno name sd
1041 赵国庆 通信
1041 赵国庆 会计
1041 赵国庆 计算机
1041 赵国庆 网络
1041 赵国庆 通信
1041 赵国庆 会计
当1041号没有学过“计算机”、“网络”课程的,那么就插入2条记录
sno name sd
1041 赵国庆 通信
1041 赵国庆 会计
1041 赵国庆 计算机
1041 赵国庆 网络
insert tablename
select distinct a.sno,a.name,b.sd
from tablename a,课程表 b
where not exists (
select 1 from tablename
where sno=a.sno
and sd=b.sd
)
select distinct a.sno,a.name,b.sd
from tablename a,(
select 通信 as sd union all select
'会计' union all select
'计算机' union all select
'网络'
) b
where not exists (
select 1 from tablename
where sno=a.sno
and sd=b.sd
)
as
begin
if not exists(select 1 from table1 where sno=@sno and sd=@sd)
begin
insert into table1(sno,sd)
select @sno,@sd
end
end
select '1041', '赵国庆' , '计算机' union all
select '1041', '赵国庆' , '网络'
where not exists(select 1 from tb where sno='1041' and [name]='赵国庆' and (sd='计算机' or sd='网络') )
(
sno char(4),
[name] varchar(10),
sd varchar(10)
)insert into tb
select '1041', '赵国庆' , '通信' union all
select '1041', '赵国庆' , '会计'-- select * from tb
--
-- delete tb where sno='1041' and [name]='赵国庆' and sd='计算机'
-- delete tb where sno='1041' and [name]='赵国庆' and sd='网络'insert into tb
select '1041', '赵国庆' , '计算机' union all
select '1041', '赵国庆' , '网络'
where not exists(select 1 from tb where sno='1041' and [name]='赵国庆' and (sd='计算机' or sd='网络') )
(
sno char(4),
[name] varchar(10),
sd varchar(10)
)insert into tb
select '1041', '赵国庆' , '通信' union all
select '1041', '赵国庆' , '会计'select * from tbinsert into tb
select '1041', '赵国庆' , '计算机'
where not exists(select 1 from tb where sno='1041' and [name]='赵国庆' and sd='计算机')
union all
select '1041', '赵国庆' , '网络'
where not exists(select 1 from tb where sno='1041' and [name]='赵国庆' and sd='网络' )