SQL中数据分页，每页固定行数，不足补空行的实例讲解

创建表

use [zws]

go

/****** object: table [dbo].[test] script date: 2018/5/17 21:26:29 ******/

set ansi_nulls on

go

set quoted_identifier on

go

create table [dbo].[test](

[id] [int] null,

[val] [varchar](50) null

) on [primary]

go

insert [dbo].[test] ([id], [val]) values (1, n'aa')

insert [dbo].[test] ([id], [val]) values (2, n'bb')

insert [dbo].[test] ([id], [val]) values (3, n'cc')

insert [dbo].[test] ([id], [val]) values (4, n'dd')

insert [dbo].[test] ([id], [val]) values (6, n'ff')

insert [dbo].[test] ([id], [val]) values (7, n'fff')

insert [dbo].[test] ([id], [val]) values (8, n'xd')

分行语句一：

/****** script for selecttopnrows command from ssms ******/

select id ,val

from dbo.test

union all

select null,null

from

(select number from master..spt_values where type='p' and number >0) bb,

(select count(*) 'totalcn',

case when 1-(count(*)/5.0%1) <1

then (1-count(*)/5.0%1)*5

else 0 end as 'maxcn'

from dbo.test

) cc

where bb.number<=cc.maxcn

分行语句二：

/****** script for selecttopnrows command from ssms ******/

select id ,val

from dbo.test

union all

select null,null

from

(select number from master..spt_values where type='p' and number >0) bb,

(select count(*) 'totalcn',

case when count(*)%5.0 <>0

then 5-count(*)%5.0

else 0 end as 'maxcn'

from dbo.test

) cc

where bb.number<=cc.maxcn