创建表

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