oracle 分区条件加函数,请教SQL Server 中是否有类似ORACLE 中分区函数patition by 的机能...

SQL codecreate table article(id int,name varchar(10),context varchar(20),typeid int,dateandtime datetime)

insert into article values(1 , 'test1', '', 1 , '2008/03/24 18:00:00')

insert into article values(2 , 'test2', '', 1 , '2008/03/24 18:01:00')

insert into article values(3 , 'test3', '', 2 , '2008/03/24 18:00:00')

insert into article values(4 , 'test4', '', 2 , '2008/03/24 18:02:00')

insert into article values(5 , 'test5', '', 2 , '2008/03/24 18:03:00')

insert into article values(6 , 'test6', '', 2 , '2008/03/24 18:04:00')

create table type(Id int , name varchar(10))

insert into type values(1 ,'国际新闻')

insert into type values(2 ,'国内新闻')

go

select m.name,m.context,n.name , m.dateandtime from

(

select t.* from article t where dateandtime in (select top 3 dateandtime from article where typeid = t.typeid order by dateandtime desc)

) m , type n

where m.typeid = n.id

drop table article, type

/*

name context name dateandtime

---------- -------------------- ---------- ------------------------------------------------------

test1 国际新闻 2008-03-24 18:00:00.000

test2 国际新闻 2008-03-24 18:01:00.000

test4 国内新闻 2008-03-24 18:02:00.000

test5 国内新闻 2008-03-24 18:03:00.000

test6 国内新闻 2008-03-24 18:04:00.000

(所影响的行数为 5 行)

*/

------解决方案--------------------

sql2005SQL codeselect row_number() over (partition by a order by b)as row ,*

from(select 'a' a,'b' b union select 'a','c' union select 'b','a')a

/*

row a b

-------------------- ---- ----

1 a b

2 a c

1 b a

(3 行受影响)*/