SQL 找出一个表中各个分类的前三条记录

SQL2000的写法一：


select  QuestionId,CategoryId,Title   from  QA_Questions a  where  
a.QuestionId <=
( select   max (c.QuestionId)  from  
( select   top   3   QuestionId   from  QA_Questions b  where  b.CategoryId = a.CategoryId) c)
order   by  CategoryId


SQL2000的写法二：
select  a.questionid,a.categoryid
from  dbo.QA_Questions a
where  Questionid  in  ( select   top   3  Questionid  from  QA_Questions  where  categoryid = a.categoryid 
order   by  Questionid  desc ) order   by  a.categoryid  asc



SQL2005的写法：

WITH  NewTable  AS (
SELECT   * ,RANK()  OVER (PARTITION  BY   CategoryId  Order   BY   QuestionId) RANK
  FROM   QA_Questions)
SELECT   *   FROM  NewTable
WHERE  RANK  <   4


-- -以下是时间测试，结果为189：266

declare   @t   datetime  ; set   @t = getdate ()
select   *   from  QA_Questions a  where  
a.QuestionId <=
( select   max (c.QuestionId)  from  
( select   top   3   QuestionId   from  QA_Questions b  where  b.CategoryId = a.CategoryId) c)
order   by  CategoryId

select   DateDiff ( ms, @t , getdate ()); set   @t = getdate ()
begin
     WITH  NewTable  AS (
     SELECT   * ,RANK()  OVER (PARTITION  BY   CategoryId  Order   BY   QuestionId) RANK
      FROM   QA_Questions)
     SELECT   *   FROM  NewTable
     WHERE  RANK  <   4
end

select   DateDiff ( ms, @t , getdate ()); set   @t = getdate ()

select  a.questionid,a.categoryid
from  dbo.QA_Questions a
where  Questionid  in  ( select   top   3  Questionid  from  QA_Questions  where  categoryid = a.categoryid 
order   by  Questionid  desc ) order   by  a.categoryid  asc

select   DateDiff ( ms, @t , getdate ())