请教SQL2005检索出两个相同表结构不同的值

[jinx151]

有两个表数据表结构相同
如：

A表：ID NAME CONTENT ADDTIME

1 测试1 测试内容1 2008-9-1

2 测试2 测试内容2 2008-9-2





B表：ID NAME CONTENT ADDTIME

1 测试1 测试内容1 2009-9-1

2 测试2 测试内容2 2009-9-2

3 测试3 测试内容3 2009-9-3

4 测试4 测试内容4 2009-9-4

请注意在不管字段ADDTIME，用效率高的SQL语句检索出结果显示：

3 测试3 测试内容3 2009-9-3

4 测试4 测试内容4 2009-9-4

[rucypli]

select * from tb1
except
select * from tb2

[aoyihuashao]

select * from t1 where checksum(*) not in (select checksum(*) from t2)

[jinx151]

rucypli,aoyihuashao,两位在SQL测试后得到的结果：

1 测试1 测试内容1 2008-9-1

2 测试2 测试内容2 2008-9-2

并不能得到我要的结果，我要的是在不管ADDTIME字段值的前提下，进行比较；得出他们的不同值：

得到结果应该B表中的：

3 测试3 测试内容3 2009-9-3

4 测试4 测试内容4 2009-9-4

[--小F--]

---2005
select * from tb1 
except 
select * from tb2

--2000
select  *  from  t1  where  checksum(*)  not  in  (select  checksum(*)  from  t2

[黄_瓜]

[Quote=引用 4 楼 fredrickhu 的回复:]
SQL code---2005select*from tb1exceptselect*from tb2--2000select*from t1where checksum(*)notin (select checksum(*)from t2
[/Quote]
学习

[水族杰纶]

select * from tb t where not exists(select 1 from ta where NAME=t.NAME and 
CONTENT=t.CONTENT)

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-12-04 13:37:49
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--	Nov 24 2008 13:01:59 
--	Copyright (c) 1988-2005 Microsoft Corporation
--	Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go 
create table [a]([ID] int,[NAME] varchar(5),[CONTENT] varchar(9),[ADDTIME] datetime)
insert [a]
select 1,'测试1','测试内容1','2008-9-1' union all
select 2,'测试2','测试内容2','2008-9-2'
--> 测试数据：[b]
if object_id('[b]') is not null drop table [b]
go 
create table [b]([ID] int,[NAME] varchar(5),[CONTENT] varchar(9),[ADDTIME] datetime)
insert [b]
select 1,'测试1','测试内容1','2009-9-1' union all
select 2,'测试2','测试内容2','2009-9-2' union all
select 3,'测试3','测试内容3','2009-9-3' union all
select 4,'测试4','测试内容4','2009-9-4'
--------------开始查询--------------------------
select * from [b] where ltrim(id)+name not in(select ltrim(id)+name  from a)
----------------结果----------------------------
/*ID          NAME  CONTENT   ADDTIME
----------- ----- --------- -----------------------
3           测试3   测试内容3     2009-09-03 00:00:00.000
4           测试4   测试内容4     2009-09-04 00:00:00.000

(2 行受影响) 
*/

[水族杰纶]

--> Title  : Generating test data [ta]
--> Author : 
--> Date   : 2009-12-04 13:40:10
if object_id('[ta]') is not null drop table [ta]
go
create table [ta] (ID int,NAME nvarchar(6),CONTENT nvarchar(10),ADDTIME datetime)
insert into [ta]
select 1,N'测试1',N'测试内容1','2008-9-1' union all
select 2,N'测试2',N'测试内容2','2008-9-2'
if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (ID int,NAME nvarchar(6),CONTENT nvarchar(10),ADDTIME datetime)
insert into [tb]
select 1,N'测试1',N'测试内容1','2009-9-1' union all
select 2,N'测试2',N'测试内容2','2009-9-2' union all
select 3,N'测试3',N'测试内容3','2009-9-3' union all
select 4,N'测试4',N'测试内容4','2009-9-4'
select * from tb t where not exists(select 1 from ta where NAME=t.NAME and 
CONTENT=t.CONTENT)
/*
ID          NAME   CONTENT    ADDTIME
----------- ------ ---------- -----------------------
3           测试3    测试内容3      2009-09-03 00:00:00.000
4           测试4    测试内容4      2009-09-04 00:00:00.000

(2 個資料列受到影響)

*/

[水族杰纶]

[Quote=引用 5 楼 beirut 的回复:]
引用 4 楼 fredrickhu 的回复:
SQL code---2005select*from tb1exceptselect*from tb2--2000select*from  t1where  checksum(*)notin  (select  checksum(*)from  t2

学习
[/Quote]
.

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-12-04 13:37:49
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--	Nov 24 2008 13:01:59 
--	Copyright (c) 1988-2005 Microsoft Corporation
--	Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go 
create table [a]([ID] int,[NAME] varchar(5),[CONTENT] varchar(9),[ADDTIME] datetime)
insert [a]
select 1,'测试1','测试内容1','2008-9-1' union all
select 2,'测试2','测试内容2','2008-9-2'
--> 测试数据：[b]
if object_id('[b]') is not null drop table [b]
go 
create table [b]([ID] int,[NAME] varchar(5),[CONTENT] varchar(9),[ADDTIME] datetime)
insert [b]
select 1,'测试1','测试内容1','2009-9-1' union all
select 2,'测试2','测试内容2','2009-9-2' union all
select 3,'测试3','测试内容3','2009-9-3' union all
select 4,'测试4','测试内容4','2009-9-4'
--------------开始查询--------------------------
select * from [b] where ltrim(id)+name+CONTENT not in(select ltrim(id)+name+CONTENT  from a)

select * from b where not exists(select 1 from a where NAME=b.NAME and 
CONTENT=b.CONTENT and a.id=b.id)
----------------结果----------------------------
/*ID          NAME  CONTENT   ADDTIME
----------- ----- --------- -----------------------
3           测试3   测试内容3     2009-09-03 00:00:00.000
4           测试4   测试内容4     2009-09-04 00:00:00.000

(2 行受影响) 
*/

[jinx151]

谢谢大家

[dzcdhj]

怎么有的回复看不到？？