欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-03-24 13:39:30
创建表 use [zws] go /****** object: table [dbo].[test] script date: 2018/5/17 21:26:29 ******/ set ans...

创建表

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