有表A,U_QTY=申请数量,U_PERIODS=供货期
U_QTY U_PERIODS
3 2010-07-05
1 2010-08-04有表B,DOCDATE 收货日期,INQTY 收货数量,
INQTY DOCDATE
4 2010-07-10
4 2010-08-05然后寻找A.U_PERIODS之后最近一次日期信息结果,结果是
3 2010-07-05 4 2010-07-10
1 2010-08-04 4 2010-08-05而不是
3 2010-07-05 4 2010-07-10
1 2010-08-04 4 2010-08-05
3 2010-07-05 4 2010-08-05
U_QTY U_PERIODS
3 2010-07-05
1 2010-08-04有表B,DOCDATE 收货日期,INQTY 收货数量,
INQTY DOCDATE
4 2010-07-10
4 2010-08-05然后寻找A.U_PERIODS之后最近一次日期信息结果,结果是
3 2010-07-05 4 2010-07-10
1 2010-08-04 4 2010-08-05而不是
3 2010-07-05 4 2010-07-10
1 2010-08-04 4 2010-08-05
3 2010-07-05 4 2010-08-05
if object_id('ta') is not null
drop table ta
go
create table ta (U_QTY int,U_PERIODS date)
insert into ta
select 3, '2010-07-05' union all
select 1, '2010-08-04'
if object_id('tb') is not null
drop table tb
go
create table tb (INQTY int,DOCDATE date)
insert into tb
select 4, '2010-07-10' union all
select 4, '2010-08-05'select U_QTY,U_PERIODS, b.INQTY,b.DOCDATE
from ta join (select tb.INQTY,tb.DOCDATE,min(DATEDIFF(D,U_PERIODS,DOCDATE)) d
from ta ,tb
where DATEDIFF(D,U_PERIODS,DOCDATE)>0
group by tb.INQTY,tb.DOCDATE) b on DATEADD(D,b.d,ta.U_PERIODS)=b.DOCDATEU_QTY U_PERIODS INQTY DOCDATE
3 2010-07-05 4 2010-07-10
1 2010-08-04 4 2010-08-05
if object_id('[A]') is not null drop table [A]
go
create table [A]([U_QTY] int,[U_PERIODS] datetime)
insert [A]
select 3,'2010-07-05' union all
select 1,'2010-08-04'
if object_id('[B]') is not null drop table [B]
go
create table [B]([INQTY] int,[DOCDATE] datetime)
insert [B]
select 4,'2010-07-10' union all
select 4,'2010-08-05'
---查询---
select
A.*,
INQTY=(select top 1 INQTY from B where DOCDATE>=A.U_PERIODS order by DOCDATE),
DOCDATE=(select top 1 DOCDATE from B where DOCDATE>=A.U_PERIODS order by DOCDATE)
from
A---结果---
U_QTY U_PERIODS INQTY DOCDATE
----------- ----------------------- ----------- -----------------------
3 2010-07-05 00:00:00.000 4 2010-07-10 00:00:00.000
1 2010-08-04 00:00:00.000 4 2010-08-05 00:00:00.000(2 行受影响)
吧