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

 
--表结构及数据:

    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    

  

  

posted @ 2015-10-30 14:04  雪卜  阅读(703)  评论(0编辑  收藏  举报