求MSSQL无序分页,谢谢了

bgk112 2010-09-25 12:44:44
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语句,求分页方法,麻烦高手指点指点.谢谢
...全文
54 点赞 收藏 1
写回复
1 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
王向飞 2010-09-25
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
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2010-09-25 12:44
社区公告
暂无公告