SQLSERVER 使用WITH函数查找时间点最大数据行

时间：2023-03-08 16:30:15
--表结构及数据：

    DTIME                                   TYPE            MONEY

2015-10-14 13:50:35.000	shopping	    20

2015-10-21 13:51:24.000	shopping	    40

2015-10-06 13:52:34.000　　    eat            10

2015-10-04 13:53:02.000	　eat	            60

2015-10-06 13:53:26.000	　study         70

2015-10-31 13:53:35.000	　study	   100

--SQL文：

with cr as (select t.DTIME,t.TYPE,t.MONEY,ROW_NUMBER() OVER

(PARTITION BY TYPE ORDER BY DTIME DESC) rn from COST t)

select DTIME,TYPE,MONEY from cr where cr.rn = 1

--结果

    DTIME                                 　 TYPE            MONEY

2015-10-06 13:52:34.000    　    eat    	             10

2015-10-21 13:51:24.000	　　shopping	     40

2015-10-31 13:53:35.000	　　study	            100

　　

　　

相关文章