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

急,请问这个SQL语句要怎么写?
有A表和B表,以下为表内容。
A表
a b c
001 111 222
002 ccc 333

B表
a b c
003 111 222
002 ccc 333

如何查询出A表和B表中不相同的记录,查询结果应该是:
结果表
a b c
001 111 222

请问这样的SQL语句要怎么写?一个SQL语句可以完成吗?

------解决方案--------------------
SQL code
SELECT A.*
FROM A LEFT JOIN B
ON A.A= B.A
AND A.B = B.B
AND A.C = B.C
WHERE B.A IS NULL

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


create table a
(
    a    varchar(10),
    b    varchar(10),
    c    int
)

create table b
(
    a    varchar(10),
    b    varchar(10),
    c    int
)
insert into a values('001','111',222)
insert into a values('002','ccc',222)
insert into b values('003','111',222)
insert into b values('002','ccc',333)


select a.* from a where a not in (select a from b)
/*
a,b,c
001,111,222

(1 行受影响)

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

create table a(a varchar(10),b varchar(10),c int)
go
create table b(a varchar(10),b varchar(10),c int)
go

insert into a select '001', '111', 222
insert into a select '002', 'ccc', 333

insert into b select '003', '111', 222
insert into b select '002', 'ccc', 333

select * from a
except
select * from b
---------
a    b    c
001    111    222

------解决方案--------------------
SQL code
use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#A') is null
    drop table #A
Go
Create table #A([a] nvarchar(3),[b] nvarchar(3),[c] int)
Insert #A
select N'001',N'111',222 union all
select N'002',N'ccc',333
Go
use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#B') is null
    drop table #B
Go
Create table #B([a] nvarchar(3),[b] nvarchar(3),[c] int)
Insert #B
select N'003',N'111',222 union all
select N'002',N'ccc',333
Go
SELECT * FROM (SELECT * FROM #A EXCEPT Select * from #B)t
 UNION all
SELECT * FROM (SELECT * FROM #B EXCEPT Select * from #A)T
/*
a    b    c
001    111    222
003    111    222
*/