林建明

下午去面试，碰到一题SQL题目，其实挺简单的，当时脑门是给挤了还是怎的都不知道，怎么也想不出来，晚上回来想了一下如下

if not object_id('t_p') is null
drop table t_p
go
if not object_id('t_c') is null
drop table t_c
go
create table t_p
(id int,cid int,cdate datetime)
go
create table t_c
(cid int,cname nvarchar(20))
go

insert into t_c(cid,cname) values(1,'A')
insert into t_c(cid,cname) values(2,'B')
insert into t_c(cid,cname) values(3,'C')
insert into t_p(id,cid,cdate) values(1,1,'2011-2-3')
insert into t_p(id,cid,cdate) values(2,1,'2011-2-4')
insert into t_p(id,cid,cdate) values(3,1,'2011-2-5')
insert into t_p(id,cid,cdate) values(4,1,'2011-2-6')
insert into t_p(id,cid,cdate) values(5,1,'2011-2-7')
insert into t_p(id,cid,cdate) values(6,1,'2011-2-8')
insert into t_p(id,cid,cdate) values(7,2,'2011-2-11')
insert into t_p(id,cid,cdate) values(8,2,'2011-2-12')
insert into t_p(id,cid,cdate) values(9,2,'2011-2-13')
insert into t_p(id,cid,cdate) values(10,2,'2011-2-14')
insert into t_p(id,cid,cdate) values(11,2,'2011-2-15')
insert into t_p(id,cid,cdate) values(12,2,'2011-2-16')
insert into t_p(id,cid,cdate) values(13,3,'2011-2-23')
insert into t_p(id,cid,cdate) values(14,3,'2011-2-24')
insert into t_p(id,cid,cdate) values(15,3,'2011-2-25')
insert into t_p(id,cid,cdate) values(16,3,'2011-2-26')
insert into t_p(id,cid,cdate) values(17,3,'2011-2-27')
insert into t_p(id,cid,cdate) values(18,3,'2011-2-28')
insert into t_p(id,cid,cdate) values(19,3,'2011-2-1')

select * from (select row_number() over(partition by cid order by cdate desc) as rowid,* from t_p) T where T.rowid<=5

这样就简单的实现了找到每个产品类别的最新五个产品，剩下的是连接类别表按类别表的其它字段排序

因为SQL2005的一些特性用得比较少，现在在想不用row_number() over()该怎么实现呢