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 ())
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 ())