SQL2000的写法一: select QuestionId,CategoryId,Title from QA_Questions a where a.QuestionId<= (selectmax(c.QuestionId) from (selecttop3 QuestionId from QA_Questions b where b.CategoryId=a.CategoryId) c) orderby CategoryId SQL2000的写法二: select a.questionid,a.categoryid from dbo.QA_Questions a where Questionid in (selecttop3 Questionid from QA_Questions where categoryid=a.categoryid orderby Questionid desc)orderby a.categoryid asc SQL2005的写法: WITH NewTable AS( SELECT*,RANK() OVER(PARTITION BY CategoryId OrderBY QuestionId) RANK FROM QA_Questions) SELECT*FROM NewTable WHERE RANK <4 ---以下是时间测试,结果为189:266 declare@tdatetime ;set@t=getdate() select*from QA_Questions a where a.QuestionId<= (selectmax(c.QuestionId) from (selecttop3 QuestionId from QA_Questions b where b.CategoryId=a.CategoryId) c) orderby CategoryId selectDateDiff( ms,@t,getdate());set@t=getdate() begin WITH NewTable AS( SELECT*,RANK() OVER(PARTITION BY CategoryId OrderBY QuestionId) RANK FROM QA_Questions) SELECT*FROM NewTable WHERE RANK <4 end selectDateDiff( ms,@t,getdate());set@t=getdate() select a.questionid,a.categoryid from dbo.QA_Questions a where Questionid in (selecttop3 Questionid from QA_Questions where categoryid=a.categoryid orderby Questionid desc)orderby a.categoryid asc selectDateDiff( ms,@t,getdate())
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