select * from A m,B n where m.id=n.id and not n.[date]=(select max([date]) from B where id=n.id)
select * from A m,B n where m.id=n.id and n.[date]=(select max([date]) from B where id=n.id)
select a.name,b.time from a cross apply (select top 1 * from b where name=a.name order by time desc)b
select * from A m,B n where m.id=n.id and not exists(select 1 from B where id=n.id and [date]>n.[date])
select a.*,isnull(c.date字段,a.date字段) as date字段 from a outer apply ( select top 1 * from b where b.租户id = a.租户id order by date字段 desc ) as c 2005+
select a.name,b.time from a,b where a.name=b.bname and b.[date]=(select max([date]) from B t where name=a.name)
2000 select m.*,isnull(n.[date],m.[date]) as [date] from A m left join B n on m.id=n.id and not exists(select 1 from B where id=n.id and [date]>n.[date])
B为已开通租户的变更表 ---用row_number函数以租户分组修改时间降序就能取得最近修改时间,然后关联下A表就好了 ;WITH DD AS( select b.*,row_number()over(partition by 租户id order by 修改时间 desc)nb from b --已开通租户的变更表 ) --查询 select A.*,DD.修改时间 from DD,A where nb=1 and dd.租户id=A.租户id
B为已开通租户的变更表 ---用row_number函数以租户分组修改时间降序就能取得最近修改时间,然后关联下A表就好了 ;WITH DD AS( select b.*,row_number()over(partition by zhid order by sj desc)nb from b --已开通租户的变更表 ) --查询 select A.*,DD.修改时间 from DD,A where nb=1 and dd.zhid=A.zhid
select * into #tb from( select zhid,sj from a union all select zhid,sj from b ) tselect * from #tb t where not exists (select 1 from #tb where zhid = t.zhid and sj > t.sj)
select m.* , n.* from a m , b n where m.关键字 = n.关键字 and n.date类型的字段 = (select max(date类型的字段) from b where 关键字 = n.关键字)select m.* , n.* from a m , b n where m.关键字 = n.关键字 and not exists (select 1 from b where 关键字 = n.关键字 and date类型的字段 > n.date类型的字段)
where m.id=n.id
and not n.[date]=(select max([date]) from B where id=n.id)
where m.id=n.id
and n.[date]=(select max([date]) from B where id=n.id)
a.name,b.time
from
a
cross apply
(select top 1 * from b where name=a.name order by time desc)b
where m.id=n.id
and not exists(select 1 from B where id=n.id and [date]>n.[date])
from a outer apply (
select top 1 * from b
where b.租户id = a.租户id
order by date字段 desc
) as c
2005+
a.name,b.time
from
a,b
where
a.name=b.bname
and
b.[date]=(select max([date]) from B t where name=a.name)
select m.*,isnull(n.[date],m.[date]) as [date]
from A m left join B n
on m.id=n.id
and not exists(select 1 from B where id=n.id and [date]>n.[date])
表B:字段有guid(主键,按照时间顺序随机生成),zhid(租户的ID对应于租户信息表中的主键),sj
要求的结果:所有租户的zhid及其对应的sj(这个时间必须是最新修改的时间,两表中最新添加记录对应的时间)。
注意:一个租户在A表中只有一条记录,在B表中可能有多条或没有记录;记录的条件顺序可通过guid判断。
B为已开通租户的变更表
---用row_number函数以租户分组修改时间降序就能取得最近修改时间,然后关联下A表就好了
;WITH
DD AS(
select b.*,row_number()over(partition by 租户id order by 修改时间 desc)nb
from b --已开通租户的变更表
)
--查询
select A.*,DD.修改时间
from DD,A
where nb=1 and dd.租户id=A.租户id
B为已开通租户的变更表
---用row_number函数以租户分组修改时间降序就能取得最近修改时间,然后关联下A表就好了
;WITH
DD AS(
select b.*,row_number()over(partition by zhid order by sj desc)nb
from b --已开通租户的变更表
)
--查询
select A.*,DD.修改时间
from DD,A
where nb=1 and dd.zhid=A.zhid
select * into #tb
from(
select zhid,sj from a
union all
select zhid,sj from b
) tselect *
from #tb t
where not exists (select 1 from #tb where zhid = t.zhid and sj > t.sj)
select m.* , n.* from a m , b n
where m.关键字 = n.关键字 and n.date类型的字段 = (select max(date类型的字段) from b where 关键字 = n.关键字)select m.* , n.* from a m , b n
where m.关键字 = n.关键字 and not exists (select 1 from b where 关键字 = n.关键字 and date类型的字段 > n.date类型的字段)