Sqlserver取最近一分组中最新一条记录
时间：2021-07-01 10:21:17
帮助过：3人阅读
1 用子查询 可以过滤掉parentID为NULL的*/
select * from Bid_ProjectPackageAlteredDesignInfo t0
where exists
(
select 1 from
(
select ParentID,
max(AlterTime)
as AlterTime
from Bid_ProjectPackageAlteredDesignInfo
group by ParentID
) x
where x.ParentID
= t0.ParentID
and x.AlterTime
= t0.AlterTime
)
select a.
*
from Bid_ProjectPackageAlteredDesignInfo a
inner join
(select ParentID,
max(AlterTime)
‘AlterTime‘
from Bid_ProjectPackageAlteredDesignInfo
group by ParentID) b
on a.ParentID
=b.ParentID
and a.AlterTime
=b.AlterTime
/*不可以过滤掉parentID为NULL的*/
select a.
*
from Bid_ProjectPackageAlteredDesignInfo a
where not exists
(
select 1 from Bid_ProjectPackageAlteredDesignInfo b
where b.ParentID
=a.ParentID
and b.AlterTime
>a.AlterTime
)
/* 2 用窗口函数 *//*不可以过滤掉parentID为NULL的*/
select * from
(
select ParentID,AlterTime
, max(AlterTime)
over(partition
by ParentID)
as AlterTimeMax
from Bid_ProjectPackageAlteredDesignInfo
) x
where AlterTimeMax
=AlterTime
Sqlserver取最近一分组中最新一条记录
标签：
