求一个Sql语句，请指点一下，谢谢！
Sql   Server2000中有这样两张表：
表A：（字段类型均为varchar）
ID           TheDate             Address     ……
001         2006-01-01       XXXXX         ……
002         2006-02-01       YYYYY         ……
……

表B：（字段类型均为varchar）
ID           TheDate                 ……
001         2006-01-05           ……
001         2006-01-09           ……
001         2006-01-14           ……
002         2006-02-08           ……
……

现在要查询出以下类型的记录：
ID           A_TheDate           B_TheDate    
001         2006-01-01         200601-20
……

即查询出，在ID相等的情况下，表A中某条记录的日期与表B中相应记录中的最大日期相差10天以上的记录。

请问这样的Sql语句怎么写，请指点一下！

学习，关注……


------解决方案--------------------
试试
select A.* from A,(select ID,max(TheDate) maxdate from 表B group by ID) B
where A.id=B.id and (datediff(day,A.thedate,b.maxdate)> 10 or datediff(day,A.thedate,b.maxdate) <-10)
------解决方案--------------------
select a.id as id,a.thedate as a_thedate,b.thedate as b_thedate
from a
inner join b
on a.id = b.id
and datediff(dd,convert(datetime,a.thedate),convert(datetime,b.thedate)) > 10
------解决方案--------------------
create table A(ID char(3), TheDate varchar(20), Address varchar(20))
insert A select '001 ', '2006-01-01 ', 'XXXXX '
union all select '002 ', '2006-02-01 ', 'YYYYY '

create table B(ID char(3), TheDate varchar(20))
insert B select '001 ', '2006-01-05 '
union all select '001 ', '2006-01-09 '
union all select '001 ', '2006-01-14 '
union all select '002 ', '2006-02-08 '


select A.ID, A_TheDate=A.TheDate, B.B_TheDate from A
inner join
(select ID, max(TheDate) as B_TheDate from B group by ID) B
on A.ID=B.ID and datediff(d, A.TheDate, B.B_TheDate)> =10

--result
ID A_TheDate B_TheDate
---- -------------------- --------------------
001 2006-01-01 2006-01-14

(1 row(s) affected)
------解决方案--------------------
select A.ID, A.TheDate, B.TheDate from A
inner join B
on
A.ID=B.ID
where
datediff(d, A.TheDate, B.TheDate)> =10
------解决方案--------------------

create table A([ID] varchar(40), TheDate varchar(40))

insert into A
select '001 ', '2006-01-01 '
union

select '002 ', '2006-01-04 '


create table B([ID] varchar(40), TheDate varchar(40))

insert into B
select '001 ', '2006-01-01 '
union
select '001 ', '2006-01-20 '
union
select '002 ', '2006-01-03 '
union
select '002 ', '2006-01-04 '