晋江信息网
时尚
当前位置:首页 > 时尚

求一个sql语句,实现在查询结果中显示每5行后加一空白行

发布时间:2019-09-13 19:15:25 编辑:笔名

一个sql语句,实现在查询结果中显示每5行后加一空白行

SQL code

exec(

select 1 as jh, ... pivot

union all

select 2 as jh, ... pivot

union all

select 3 as jh, ... pivot

union all

select 4 as jh, ... pivot

union all

select 5 as jh, ... pivot

)

查询结果为:

1 ...

2 ...

3 ...

4 ...

5 ...

1 ...

2 ...

3 ...

4 ...

5 ...

1 ...

2 ...

3 ...

4 ...

5 ...

......

其中这个查询有30列

SQL code

union all

select "" as jh ...

30列啊其中还有pivot转化的列

SQL codeCREATE TABLE [TB] (

[ID] [int] IDENTITY (1, 1) NOT NULL ,

[ITEM] [int] NULL ,

[NOTE] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,

CONSTRAINT [PK_TB] PRIMARY KEY CLUSTERED

(

[ID]

) ON [PRIMARY]

) ON [PRIMARY]

GO

INSERT INTO TB (ITEM, NOTE) SELECT 3,"XX"

INSERT INTO TB (ITEM, NOTE) SELECT 3,"XX"

INSERT INTO TB (ITEM, NOTE) SELECT 4,"XX"

INSERT INTO TB (ITEM, NOTE) SELECT 5,"XX"

INSERT INTO TB (ITEM, NOTE) SELECT 5,"XX"

INSERT INTO TB (ITEM, NOTE) SELECT 6,"XX"

SELECT * FROM TB

/*-----------------------

3 XX

3 XX

4 XX

5 XX

5 XX

6 XX

*/

SELECT

ITEM=CASE WHEN ID=(

SELECT MIN(ID)

FROM TB

WHERE ITEM=A.ITEM

)

THEN RTRIM(ITEM) ELSE "" END,

NOTE

FROM TB AS A

ORDER BY ID

/*

ITEM NOTE

------------ --------------------

3 XX

XX

4 XX

5 XX

XX

6 XX

SELECT

ITEM,

NOTE

FROM (

SELECT

RTRIM(ITEM) AS ITEM,NOTE,ITEM AS order1,0 AS order2

FROM TB

UNION ALL

SELECT "","",ITEM,1

FROM TB

GROUP BY ITEM

) AS A

ORDER BY order1,order2

3 XX

3 XX

4 XX

5 XX

5 XX

6 XX

SQL codeCREATE TABLE [TB] (

[ID] [int] IDENTITY (1, 1) NOT NULL ,

[ITEM] [int] NULL ,

[NOTE] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,

CONSTRAINT [PK_TB] PRIMARY KEY CLUSTERED

(

[ID]

) ON [PRIMARY]

) ON [PRIMARY]

GO

INSERT INTO TB (ITEM, NOTE) SELECT 3,"XX"

INSERT INTO TB (ITEM, NOTE) SELECT 3,"XX"

INSERT INTO TB (ITEM, NOTE) SELECT 4,"XX"

INSERT INTO TB (ITEM, NOTE) SELECT 5,"XX"

INSERT INTO TB (ITEM, NOTE) SELECT 5,"XX"

INSERT INTO TB (ITEM, NOTE) SELECT 6,"XX"

SELECT * FROM TB

/*-----------------------

3 XX

3 XX

4 XX

5 XX

5 XX

6 XX

*/

SELECT

ITEM=CASE WHEN ID=(

SELECT MIN(ID)

FROM TB

WHERE ITEM=A.ITEM

)

THEN RTRIM(ITEM) ELSE "" END,

NOTE

FROM TB AS A

ORDER BY ID

/*

ITEM NOTE

------------ --------------------

3 XX

XX

4 XX

5 XX

XX

6 XX

SELECT

ITEM,

NOTE

FROM (

SELECT

RTRIM(ITEM) AS ITEM,NOTE,ITEM AS order1,0 AS order2

FROM TB

UNION ALL

SELECT "","",ITEM,1

FROM TB

GROUP BY ITEM

) AS A

ORDER BY order1,order2

3 XX

3 XX

4 XX

5 XX

5 XX

6 XX

孩子咳嗽吃什么药效果好
冠心病是什么原因导致的
三个月宝宝止咳方法
宝宝脸色发黄什么原因