求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语句,求分页方法,麻烦高手指点指点.谢谢
...全文
76 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
  • 打赏
  • 举报
回复
发帖
疑难问题

2.1w+

社区成员

MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
加入社区
帖子事件
创建了帖子
2010-09-25 12:44
社区公告
暂无公告