sql取所有记录中每天的最后一笔交易的记录

问题的缘起是回答一个网友的提问： http://space.cnblogs.com/question/1439/

假定有一张表TA有字段id:主键，createTime 操作时间，volumeOfBusiness 金额;现在的需求是要查每天的最后一笔交易的记录。

if   object_id ( ' ta ' , ' U ' )  is   not   null  
drop   table  ta; 
go  
create   table  ta( 
id  int   identity ( 1 , 1 )  not   null  , 
volumeOfBusiness  nvarchar ( 100 )  not   null , 
createTime  datetime   not   null  
) 
go  
insert   into  ta  values ( ' A ' , ' 2008-05-01 10:10:11 ' ); 
insert   into  ta  values ( ' B ' , ' 2008-05-01 20:10:11 ' ); 
insert   into  ta  values ( ' C ' , ' 2008-05-02 09:10:11 ' ); 
insert   into  ta  values ( ' D ' , ' 2008-05-02 15:10:11 ' ); 
insert   into  ta  values ( ' E ' , ' 2008-05-03 11:10:11 ' ); 
Go  
with  ta_temp  as ( 
select  id, volumeOfBusiness ,createTime, 
datePartOfCreateTime  =   cast ( year (createTime)  as   char ( 4 ))  +   ' - '  
+   cast ( month (createTime)  as   char ( 2 ))  +   ' - '  
+   cast ( day (createTime)  as   char ( 2 )) 
FROM  ta  
), 
 ta_temp_rn  as ( 
SELECT  id,volumeOfBusiness ,createTime,datePartOfcreateTime, 
rn  =  ROW_NUMBER()  OVER  (PARTITION  BY  datePartOfcreateTime  ORDER   BY  createTime  DESC ) 
  FROM  ta_temp 
) 
SELECT  id,volumeOfBusiness ,createTime  FROM  ta_temp_rn  WHERE  rn  =   1  

以上脚本是2005下的实现，用了 ROW_NUMBER()函数和 CTE表达式。

当然在sql 2000中也是可以实现的，不过实现的效率要低很多，请看下面的脚本：

select  ta. *   from  ta  inner   join   
( 
select  maxcreateTime  =   max (createTime), 
datePartOfcreateTime  =   cast ( year (createTime)  as   char ( 4 ))  +   ' - '  
+   cast ( month (createTime)  as   char ( 2 ))  +   ' - '  
+   cast ( day (createTime)  as   char ( 2 )) 
FROM  ta  group   by   
cast ( year (createTime)  as   char ( 4 ))  +   ' - '  
+   cast ( month (createTime)  as   char ( 2 ))  +   ' - '  
+   cast ( day (createTime)  as   char ( 2 )) 
)  temp    on  ta.createTime  =   temp .maxcreateTime