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语句怎么写,请指点一下!学习,关注……
表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)
from a
inner join b
on a.id = b.id
and datediff(dd,convert(datetime,a.thedate),convert(datetime,b.thedate)) > 10
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)
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))----------ResultID A_TheDate B_TheDate
001 2006-01-01 2006-01-20(1 件処理されました)
select A.[ID], A.TheDate as A_TheDate,B_TheDate
from A left join
(select [ID],max(B.TheDate) as B_TheDate from B group by [ID]) as C
on A.[ID] = C.[ID]
where B_TheDate> dateadd(day, 10, A.TheDate)
select a.ID,a.TheDate A_TheDate,b.TheDate B_TheDate
from #A a,#B b
where a.ID=b.ID
and (Datediff(Day,a.TheDate,b.TheDate)>=10 or Datediff(Day,a.TheDate,b.TheDate)<=-10)