问题的缘起是回答一个网友的提问：
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
posted on 2008-05-23 16:30
玉开 阅读(1628)
评论(12) 编辑 收藏 所属分类:
数据库