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