日期:2014-05-18  浏览次数:20523 次

求两个表列与行的差异对比的sql 语句
有两个两个表table1 table2
table1如下:
  row1 row2
  a o
  b p 
  c q
  d s 

table2如下
  row1 row2
  a o
  b p
  c x 
  d y
  e z

我想找出这两个边的差异并输出 table3
结果应该为
  row1 row2
  c x 
  d y
  e z

这个sql 语句该怎么写,




------解决方案--------------------
SQL code

select * from table1 a inner join table2 b on a.row1 = b.row1 and a.row2<>b.row2

------解决方案--------------------
SQL code
select * from tb2 except select * from tb1

------解决方案--------------------
select * table2 a where not exists(select 1 from table1 b where a.col1=b.col1 and a.col2<>b.col2)
------解决方案--------------------
SQL code
select * from tb2 a where not exists(
select 1 from tb1 b where a.row1=b.row1 and a.row2=b.row2)

------解决方案--------------------
SQL code

--> 测试数据:[table1]
if object_id('[table1]') is not null drop table [table1]
create table [table1]([row1] varchar(1),[row2] varchar(1))
insert [table1]
select 'a','o' union all
select 'b','p' union all
select 'c','q' union all
select 'd','s'
--> 测试数据:[table2]
if object_id('[table2]') is not null drop table [table2]
create table [table2]([row1] varchar(1),[row2] varchar(1))
insert [table2]
select 'a','o' union all
select 'b','p' union all
select 'c','x' union all
select 'd','y' union all
select 'e','z'

select * from table2 a--给table2取别名a
where not exists(select 1 from table1 b --给table1取别名a
where a.row1=b.row1 and a.row2=b.row2)

/*
row1    row2
c    x
d    y
e    z
*/