select
t1.id,dbo.t1.name,dbo.t1.rq,t2.dj
from
t1
cross apply
(select top 1 dj from dbo.t3 where t1.id = t1_id and t1.rq >= sxsj order by sxsj desc) as t2
就是分组求第一把 试试这个
t1.id,dbo.t1.name,dbo.t1.rq,t2.dj
from
t1
cross apply
(select top 1 dj from dbo.t3 where t1.id = t1_id and t1.rq >= sxsj order by sxsj desc) as t2
就是分组求第一把 试试这个
select
a.id,a.name,b.dj
from
t1 as a inner join t3 as b on a.id=b.t1_id
and
not exists(select 1 from t3 where t1_id=b.t1_id and sxsj>b.sxsj)
第一种用OUTER APPLY
第二种用LEFT JOIN
本例中我倾向于在价格表中加一个失效日期。
[code=SQL]create table t3
(
t1_id int,
dj float,
sxsj datetime,
expDate datetime
)
goinsert into t3
select 1,3000,'2013-10-01','2013-10-11' union
select 1,3200,'2013-10-11','2013-10-31' union
select 1,3100,'2013-10-31','9999-12-31' union
...
goselect t1.id,t1.name,t1.rq,t3.dj
from t1
left join t3
on t3.t1_id = t1.id
and t3.sxsj <= t1.rq
and t1.rq < t3.expDate
[/sql]
第一种用OUTER APPLY
第二种用LEFT JOIN
搞定 多谢小F姐姐