Sqlserver取最近一分組中最新一條記錄

標籤：

/* 1 用子查詢 可以過濾掉parentID為NULL的*/select * from Bid_ProjectPackageAlteredDesignInfo t0where 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 AlterTimeMaxfrom Bid_ProjectPackageAlteredDesignInfo) xwhere AlterTimeMax =AlterTime

 

Sqlserver取最近一分組中最新一條記錄