# 求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

...全文
56 1 点赞 打赏 收藏 举报

1 条回复

``````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