select m.* , n.* from a m , b n where m.id = n.id and n.dt = (select max(dt) from b and id = n.id)select m.* , n.* from a m , b n where m.id = n.id and not exists (select 1 from b and id = n.id and dt > n.dt)
select * from tb1 a,tb2 b where a.id=b.id and not exists (select 1 from tb2 where id=b.id and [date]>b.[date])
select * from tb1 a,tb2 b where a.id=b.id and b.id not in(select id from tb2 where id=b.id and [date]>b.[date])
楼主要的sql应该结构就这样: SELECT (A.Rate*B.Item_Price) AS CurrentPrice FROM A LEFT JOIN (SELECT Item_Price,Item_Number FROM (SELECT *,RANK() OVER ( PARTITION BY ITEM_NUMBER ORDER BY END_DATE DESC) RNK FROM B) T WHERE RNK = 1) C ON A.Item_Number = C.Item_Number
SELECT * FROM 表A a OUTER APPLY (SELECT TOP(1) * FROM 表B WHERE id = a.id ORDER BY [date] DESC) b
select a.*, b.* from a,b where not exists (select 1 from a ta,b tb where ta.id=a.id and abs(datediff(ss,ta.操作时间,tb.操作时间))<abs(datediff(ss,a.操作时间,b.操作时间)))
from a m , b n
where m.id = n.id and n.dt = (select max(dt) from b and id = n.id)select m.* , n.*
from a m , b n
where m.id = n.id and not exists (select 1 from b and id = n.id and dt > n.dt)
where a.id=b.id and not exists (select 1 from tb2 where id=b.id and [date]>b.[date])
where a.id=b.id and b.id not in(select id from tb2 where id=b.id and [date]>b.[date])
LEFT JOIN (SELECT Item_Price,Item_Number
FROM (SELECT *,RANK() OVER ( PARTITION BY ITEM_NUMBER ORDER BY END_DATE DESC) RNK FROM B) T
WHERE RNK = 1) C ON A.Item_Number = C.Item_Number
OUTER APPLY
(SELECT TOP(1) * FROM 表B WHERE id = a.id ORDER BY [date] DESC) b
from a,b
where not exists
(select 1 from a ta,b tb where ta.id=a.id
and abs(datediff(ss,ta.操作时间,tb.操作时间))<abs(datediff(ss,a.操作时间,b.操作时间)))