SQL语句求两表的差异

[log2321]

A表
ID NO
1 a1
2 a2
3 a3
4 a4
5 a5

B表
ID NO
1 a2
2 a4
3 a5

要得到C表
ID NO
1 a1
2 a3

[hpx021131]

应该是

select NO from A
except
select NO from B

因为存在NO相同而ID不同，楼主只需要NO不同而已

[sinpoal]

-----table---------
create table t6(
id number ,
no varchar(3))
create table t7(
id number,
no varchar(3))


----------sql statement----------

select b.no from 
(select t6.* ,t7.no no1 from t6 full join t7 on t6.no=t7.no) b
where no is null or no1 is null
---result-----
/*1	a3
2	a1
*/
/*用except 不完全正确，因为这里的数据是 B表中的no 在A 表中有，当然可用，如果两表都有互不存在的no的value 。在这里用全连接 判断 问题则可以解决*/

[sinpoal]

在 oracle 环境下测试的。 这里跟在ms-sqlserver下差不多

[sinpoal]

-----Oracle enviroment---------
/*  t6: id      no
	1	a1
	2	a2
	3	a3
	4	a4
	5	a5
	6	a6*/
/*  t7: id      no 
	3	a5
	2	a4
	1	a2
	4	a8*/

---sql statment---------
select concat(b.no,b.no1) no  from 
(select t6.* ,t7.no no1 from t6 full join t7 on t6.no=t7.no) b
where no is null or no1 is null order by no 
/*----result---------
1	a1
2	a3
3	a6
4	a8
*/

[fpzgm]

select * from A
where NO not in
(select No From B)

[fpzgm]

SQL code
select * from A where NO not in (select No From B)
[/Quote]

[fpzgm]

[Quote=引用 6 楼 sinpoal 的回复:]
SQL code

-----Oracle enviroment---------
/* t6: id no
1 a1
2 a2
3 a3
4 a4
5 a5
6 a6*/
/* t7: id no
3 a5
2 a4
1 ……
[/Quote] 怎么样才显示成sql code的样子

[appetizing_fish]

select * from A where NO not in (select No From B)

[hao1hao2hao3]

select * from a
except
select * from b

[juyamei126]

SELECT ROW_NUMBER() over (order by getdate()),NO   FROM A where NO not in (select No From B)