select a.*,b.* from t1 a inner join t2 b on a.id=b.aid 
where not exists(select 1 from t2  where aid=b.aid and time>b.time)

------解决方案--------------------
SQL code
;
WITH    tmp
          AS ( SELECT   * ,
                        rn = row_number() OVER ( PARTITION BY col ORDER BY time DESC )
               FROM     从表
             )
    SELECT  *
    FROM    主表 a ,
            tmp b
    WHERE   a.col = b.col
            AND b.rn = 1

------解决方案--------------------
SQL code
select * from t1 a , t2 b where  a.id=b.aid 
and time=(select max(time) from t2  where aid=b.aid )

--也可以这样

------解决方案--------------------
探讨


测试了下 好像输出不对

列1     列2
20000	2011/12/6 17:31:25
20000	2011/12/6 17:31:53
500	2011/12/14 14:33:50
10000	2011/12/14 14:34:57
10000	2011/12/14 14:36:38

输出的是 500 不是 10000

引用 1 楼 qianji……


------解决方案--------------------
主表a，从表b。
select top 1 * from a,b where a.id=b.aid order by b.time desc

------解决方案--------------------
SQL code
select * from t1 a , t2 b where  a.id=b.aid and time=(select max(time) from t2  where aid=b.aid )

------解决方案--------------------
select a.id,b.value,b.time from #a a join #b b on a.id=b.id
where exists(select 1 from #b where id=#b.id and datediff(second,(select MAX(time) from #b),b.time)>=0)