求一个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 '
============================
select A.[ID], A.TheDate as A_TheDate, convert(nvarchar(10), B_TheDate, 120) as B_TheDate
from A left join
(select [ID],max(convert(smalldatetime, B.TheDate)) as B_TheDate from B group by [ID]) as C
on A.[ID] = C.[ID]
where convert(smalldatetime,B_TheDate)> dateadd(day, 10, convert(smalldatetime,A.TheDate))
----------Result
IDA_TheDateB_TheDate
0012006-01-012006-01-20
(1 件処理されました)
