求MSSQL无序分页,谢谢了

[bgk112]

create table tableA
(
AID int,
AState int
)

insert into tableA values(1,0)
insert into tableA values(2,0)
insert into tableA values(3,1)
insert into tableA values(4,0)
insert into tableA values(5,0)
insert into tableA values(6,0)
insert into tableA values(7,1)
insert into tableA values(8,0)

create table tableB
(
BID int,
BState int,
BType int,
AID int
)

insert into tableB values(1,0,0,1)
insert into tableB values(2,1,0,8)
insert into tableB values(3,1,0,2)
insert into tableB values(4,0,0,7)
insert into tableB values(5,0,0,NULL)
insert into tableB values(6,0,0,5)
insert into tableB values(7,1,0,NULL)
insert into tableB values(8,1,0,1)
insert into tableB values(9,1,0,NULL)
insert into tableB values(10,0,0,3)

select tableA.AID,isnull(NewField,'0')as NewField,tableA.AState from
tableA left join
(select AID,abs(1-min(BState))as NewField from tableB where AID>=1 group by AID) t
on tableA.AID=t.AID order by NewField desc,AState asc

以上为测试数据及查询SQL语句，求分页方法，麻烦高手指点指点.谢谢

[王向飞]

DECLARE @pagenum AS INT, @pagesize AS INT 
SET @pagenum = 2 
SET @pagesize = 3 
SELECT * 
FROM (SELECT ROW_NUMBER() OVER(ORDER BY NewField DESC,AState asc) AS rownum, 
       tableA.AID,isnull(NewField,'0')as NewField,tableA.AState from
tableA left join
(select AID,abs(1-min(BState))as NewField from tableB where AID>=1 group by AID) t
on tableA.AID=t.AID 
) AS D 
WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize 
ORDER BY rownum