insert into a1(a,b,c)values('a1','a2','a3')
insert into a1(a,b,c)values('b1','b2','b3')
insert into a1(a,b,c)values('c1','a2','c3')insert into a2(a,b,c)values('a1','a2','a33')
insert into a2(a,b,c)values('b1','b2','b33')怎样将表a1中的第三条记录插入到表2中(条件是a和b字段内容不相等的就插入到表a2)
insert into a1(a,b,c)values('b1','b2','b3')
insert into a1(a,b,c)values('c1','a2','c3')insert into a2(a,b,c)values('a1','a2','a33')
insert into a2(a,b,c)values('b1','b2','b33')怎样将表a1中的第三条记录插入到表2中(条件是a和b字段内容不相等的就插入到表a2)
select a,b,c
from a1
where a+'+'+b not in (select a+'+'+b from a1)
where not exists(select 1 from a2 where a=t.a and b=t.b)
select * from a1
where exists(select 1 from a2 where a1.a!=a2.a and a1.b!=a2.b)
a2
select
*
from
a1
where
not exists(select 1 from a2 where a = a1.a and b = a1.b)
select r.* from a1 r,a2 t
where r.a=t.a and r.b=t.b
and r.c<>t.c
insert into a1(a,b,c)values('a1','a2','a3')
insert into a1(a,b,c)values('b1','b2','b3')
insert into a1(a,b,c)values('c1','a2','c3') create table a2(a varchar(10),b varchar(10),c varchar(10))
insert into a2(a,b,c)values('a1','a2','a33')
insert into a2(a,b,c)values('b1','b2','b33') insert into a2 select * from a1 where not exists(select 1 from a2 where a = a1.a and b = a1.b)select * from a2drop table a1 , a2/*
a b c
---------- ---------- ----------
a1 a2 a33
b1 b2 b33
c1 a2 c3(所影响的行数为 3 行)
*/
select * from a1
where exists(select 1 from a2 where a1.a!=a2.a and a1.b!=a2.b)
SELECT *
FROM a1 tb
WHERE not exists
(
SELECT 1
FROM a2
WHERE
a=tb.a and
b=tb.b
)
insert into a1(a,b,c)values('b1','b2','b3')
insert into a1(a,b,c)values('c1','a2','c3') insert into a2(a,b,c)values('a1','a2','a33')
insert into a2(a,b,c)values('b1','b2','b33')
--怎样将表a1中的第三条记录插入到表2中(条件是a和b字段内容不相等的就插入到表a2)
insert into a2 select * from a1
where not exists(select 1 from a2 where a = a1.a and b = a1.b)
select * from a1 t
where not exists
(select 1 from a2 where a=t.a and b=t.b)