多表关联的一个问题
主表和从表 一对多的关系
现在需要在取出主笔数据的数据 同时取出 从表对应数据中 列 time 值最大的一个
请问怎么写语句
[解决办法]
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)
[解决办法]
;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
[解决办法]
select * from t1 a , t2 b where a.id=b.aid and time=(select max(time) from t2 where aid=b.aid )--也可以这样
[解决办法]
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)