sql分页查询
数据如下
ID HasChild Lev
1 0 1
2 0 1
3 2 2
4 2 2
5 3 3
6 0 1
7 0 1
8 0 1
9 0 1
10 0 1
11 10 2
12 0 1
13 12 2
14 0 1
15 14 2
这是一个树形结构 我的需求是每页显示五条，但是这五条必须包括根节点和子节点
分页效果如下
第一页：
1 0 1
2 0 1
3 2 2
4 2 2
5 3 3
6 0 1
7 0 1
8 0 1
第二页
9 0 1
10 0 1
11 10 2
12 0 1
13 12 2
14 0 1
15 14 2
sql? 分页查询
[解决办法]
select 1 ID ,0 HasChild ,1 Lev
into #temp
union all select 2 ,0 ,1
union all select 3 ,2 ,2
union all select 4 ,2 ,2
union all select 5 ,3 ,3
union all select 6 ,0 ,1
union all select 7 ,0 ,1
union all select 8 ,0 ,1
union all select 9 ,0 ,1
union all select 10 ,0 ,1
union all select 11 ,10 ,2
union all select 12 ,0 ,1
union all select 13 ,12 ,2
union all select 14 ,0 ,1
union all select 15 ,14 ,2
declare @pagenum int=1;
declare @pagerownum int=5;
with t as
(
select id
from
(
select top (@pagerownum*@pagenum) *,ROW_NUMBER() over(order by id) rn
from #temp
where lev=1
) t
where rn>@pagerownum*(@pagenum-1)
)
select *
from #temp
where id in(select id from t)
or haschild in (select id from t)
create table q10
(ID int,HasChild int,Lev int)
insert into q10
select 1, 0, 1 union all
select 2, 0, 1 union all
select 3, 2, 2 union all
select 4, 2, 2 union all
select 5, 3, 3 union all
select 6, 0, 1 union all
select 7, 0, 1 union all
select 8, 0, 1 union all
select 9, 0, 1 union all
select 10, 0, 1 union all
select 11, 10, 2 union all
select 12, 0, 1 union all
select 13, 12, 2 union all
select 14, 0, 1 union all
select 15, 14, 2
-- test1
declare @pageid int
select @pageid=1
;with t as
(select ID,row_number() over(order by ID) 'rn'
from q10 where Lev=1),
u as
(select a.ID,a.HasChild,a.Lev,
case when b.ID is not null then (b.rn-1)/5+1
else (select top 1 (c.rn-1)/5+1 from t c
where c.ID<a.ID order by c.ID desc) end 'pid'
from q10 a
left join t b on a.ID=b.ID
)
select ID,HasChild,Lev from u where pid=@pageid
/*
ID HasChild Lev
----------- ----------- -----------
1 0 1
2 0 1
3 2 2
4 2 2
5 3 3
6 0 1
7 0 1
8 0 1
(8 row(s) affected)
*/
-- test2
declare @pageid int
select @pageid=2
;with t as
(select ID,row_number() over(order by ID) 'rn'
from q10 where Lev=1),
u as
(select a.ID,a.HasChild,a.Lev,
case when b.ID is not null then (b.rn-1)/5+1
else (select top 1 (c.rn-1)/5+1 from t c
where c.ID<a.ID order by c.ID desc) end 'pid'
from q10 a
left join t b on a.ID=b.ID
)
select ID,HasChild,Lev from u where pid=@pageid
/*
ID HasChild Lev
----------- ----------- -----------
9 0 1
10 0 1
11 10 2
12 0 1
13 12 2
14 0 1
15 14 2
(7 row(s) affected)
*/
if OBJECT_ID('tb') is not null
drop table tb
go
create table tb(ID int,HasChild int,Lev int)
insert into tb
select 1, 0, 1
union all select 2 ,0 ,1
union all select 3 ,2 ,2
union all select 4 ,2 ,2
union all select 5 ,3 ,3
union all select 6 ,0 ,1
union all select 7 ,0 ,1
union all select 8 ,0 ,1
union all select 9 ,0 ,1
union all select 10 ,0 ,1
union all select 11 ,10 ,2
union all select 12 ,0 ,1
union all select 13 ,12 ,2
union all select 14 ,0 ,1
union all select 15 ,14 ,2
go
declare @page int=1;
declare @pagerownum int=5;
;with t
as
(
select id,HasChild,Lev,
ROW_NUMBER() over(partition by case when lev = 1 then 0 else 1 end
order by id) as rownum
from tb
)
select ID,HasChild,Lev
from
(
select *,
case when lev = 1
then (rownum - 1) / @pagerownum
else (rownum - 1) / 3
end as page
from t
)tt
where page = @page - 1
order by ID
/*
IDHasChildLev
101
201
322
422
533
601
701
801
*/
