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