如果SJ 是datatime 类型字段 精确到秒 select top 1 a.xh,a.xm b.sj,b,nr from a join b on a.xh=b.xh order by datediff(second,b.sj,getdate()) asc
select top 1 a.xh,a.xm b.sj,b,nr from a join b on a.xh=b.xh order by abs(datediff(second,b.sj,getdate())) asc
select a.*,b.* from tablea a,tableb b where a.xh=b.xh and b.sj=getdate()-(select min(getdate()-sj) from tableb)
我这么写的: select * from tablea as a inner join (select xh,max(sj) as sj,nr from tableb group by xh) as b on a.xh=b.xh但是会出现如下错误提示: 列 'tableb.NR' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
select * from tablea as a inner join (select xh,max(sj) as sj,min(nr) as nr from tableb group by xh) as b on a.xh=b.xh
yang_: 这样得到的记录sj和nr可能不会产生自表b的同一条记录
select * from tablea as a inner join (select xh,sj,nr from tableb b1 where sj=(select max(sj) from tableb where xh=b1.xh)) as b on a.xh=b.xh
select top 1 a.xh,a.xm b.sj,b,nr from a join b on a.xh=b.xh order by datediff(second,b.sj,getdate()) asc
select a.*,b.*
from tablea a,tableb b
where a.xh=b.xh and
b.sj=getdate()-(select min(getdate()-sj) from tableb)
select * from tablea as a
inner join
(select xh,max(sj) as sj,nr from tableb group by xh) as b
on a.xh=b.xh但是会出现如下错误提示:
列 'tableb.NR' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
inner join
(select xh,max(sj) as sj,min(nr) as nr from tableb group by xh) as b
on a.xh=b.xh
这样得到的记录sj和nr可能不会产生自表b的同一条记录
inner join
(select xh,sj,nr from tableb b1 where sj=(select max(sj) from tableb where xh=b1.xh)) as b
on a.xh=b.xh