创建表
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