表test1
type name quantity
a a1 2
b b1 3
c c1 4test2
type name quantity
a a1 2
b b1 3
c c1 5我想通过查询语句找出表test1跟test2中不同的数据理想中的答案是:
tpye name quantity
c c1 5我自己这么写的:select a.type,a.name,a.quantity
from test1 a inner join test2 b
on a.type <> b.type and
a.name <> b.name and
a.quantity <> b.quantity但出来的结果不对。望各位高手不吝赐教。
type name quantity
a a1 2
b b1 3
c c1 4test2
type name quantity
a a1 2
b b1 3
c c1 5我想通过查询语句找出表test1跟test2中不同的数据理想中的答案是:
tpye name quantity
c c1 5我自己这么写的:select a.type,a.name,a.quantity
from test1 a inner join test2 b
on a.type <> b.type and
a.name <> b.name and
a.quantity <> b.quantity但出来的结果不对。望各位高手不吝赐教。
from test1 a
where not exists
(select 1 from test2 b where a.type=b.type and a.name=b.name and a.quantity=b.quantity)
type name quantity
a a1 2
b b1 3
c c1 4
c c1 5
union
select type,name, quantity from test2
(select type,name, quantity from test1
union
select type,name, quantity from test2 )Utab
type1 char(1),
Name1 char(2),
quantity int
)Create table test2(
type1 char(1),
Name1 char(2),
quantity int
)insert into test1
select 'a','a1',2 union all
select 'b','b1',3 union all
select 'c','c1',4insert into test2
select 'a','a1',2 union all
select 'b','b1',3 union all
select 'c','c1',5
select *
from test2
where type1 not in
(select t1.type1 from test1 t1,test2 t2 where t1.type1=t2.type1 and t1.name1=t2.name1 and t1.quantity=t2.quantity)--结果---
c c1 5