求一个sql语句，实现在查询结果中显示每5行后加一空白行
exec(select 1 as jh, ... pivotunion alldal
union all
select '' as jh ...
CREATE 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]GOINSERT 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 XX3 XX4 XX5 XX5 XX6 XX*/SELECT ITEM=CASE WHEN ID=( SELECT MIN(ID) FROM TB WHERE ITEM=A.ITEM ) THEN RTRIM(ITEM) ELSE '' END, NOTEFROM TB AS AORDER BY ID/*ITEM NOTE------------ --------------------3 XX XX4 XX5 XX XX6 XXSELECT ITEM, NOTEFROM ( 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 AORDER BY order1,order23 XX3 XX4 XX5 XX5 XX6 XX
[解决办法]
有点棘手 帮顶
[解决办法]
不太明白！帮顶！
[解决办法]
老师的
[解决办法]
一般这种我都在前台实现
[解决办法]
看了半天，还是不明白
[解决办法]
这功能应该交给前台去做!定！
[解决办法]
--你希望隔的行数
set @rowNum=3
select @countNum=count(*) from #a
set @rows=@countNum/@rowNum
set @i=0
while @i< @rows
begin
set @i=1+@i
insert into #a
select @i*@rowNum as rowNum,0 '产品','' as '销售类型'
end
select [产品],[销售类型]
from #a order by rowNum
drop table #a
这里用了临时表，
数字列用SQL是无法 以 '' 显示，要不在插入临时表是转化为 字符型，或前台做，
[解决办法]
牛很。
[解决办法]
放到前端去处理吧。sql是一个列集操作强的工具。
[解决办法]
非得在SQL里面解决吗？
[解决办法]
If not object_id('[tb]') is null Drop table [tb]GoCreate table [tb]([ID] int,[P_TITLE] nvarchar(6),[P_COMPANY] nvarchar(3),[P_TIME] Datetime)Insert tbSelect 1,'aaaaaa','公司1','2009-08-05' union allSelect 2,'bbbbbb','公司1','2009-08-05' union allSelect 3,'vvvvvv','公司1','2009-08-05' union allSelect 4,'cccccc','公司2','2009-08-05' union allSelect 5,'dddddd','公司2','2009-08-05' union allSelect 6,'eeeeee','公司2','2009-08-05' union allSelect 7,'ffffff','公司3','2009-08-05' union allSelect 8,'gggggg','公司3','2009-08-05' union allSelect 9,'hhhhhh','公司3','2009-08-05'Go--Select * from tb-->SQL查询如下:;with t as( select rn=(row_number()over(order by id)-1)/5,*,px=0 from tb union all select (id-1)/5,null,null,null,null,1 from tb group by (id-1)/5)select [ID],[P_TITLE],[P_COMPANY],[P_TIME] from torder by rn,px/*ID P_TITLE P_COMPANY P_TIME----------- ------- --------- -----------------------1 aaaaaa 公司1 2009-08-05 00:00:00.0002 bbbbbb 公司1 2009-08-05 00:00:00.0003 vvvvvv 公司1 2009-08-05 00:00:00.0004 cccccc 公司2 2009-08-05 00:00:00.0005 dddddd 公司2 2009-08-05 00:00:00.000NULL NULL NULL NULL6 eeeeee 公司2 2009-08-05 00:00:00.0007 ffffff 公司3 2009-08-05 00:00:00.0008 gggggg 公司3 2009-08-05 00:00:00.0009 hhhhhh 公司3 2009-08-05 00:00:00.000NULL NULL NULL NULL(11 行受影响)*/
[解决办法]
--> 生成测试数据表:tbIf not object_id('[tb]') is null Drop table [tb]GoCreate table [tb]([ID] int,[P_TITLE] nvarchar(6),[P_COMPANY] nvarchar(3),[P_TIME] Datetime)Insert tbSelect 1,'aaaaaa','公司1','2009-08-05' union allSelect 2,'bbbbbb','公司1','2009-08-05' union allSelect 3,'vvvvvv','公司1','2009-08-05' union allSelect 4,'cccccc','公司2','2009-08-05' union allSelect 5,'dddddd','公司2','2009-08-05' union allSelect 6,'eeeeee','公司2','2009-08-05' union allSelect 7,'ffffff','公司3','2009-08-05' union allSelect 8,'gggggg','公司3','2009-08-05' union allSelect 9,'hhhhhh','公司3','2009-08-05'Go--Select * from tb-->SQL查询如下:;with t as( select rn=(row_number()over(order by id)-1)/5,*,px=0 from tb union all select rn,null,null,null,null,1 from ( select rn=(row_number()over(order by id)-1)/5 from tb ) as a group by rn)select [ID],[P_TITLE],[P_COMPANY],[P_TIME] from torder by rn,px/*ID P_TITLE P_COMPANY P_TIME----------- ------- --------- -----------------------1 aaaaaa 公司1 2009-08-05 00:00:00.0002 bbbbbb 公司1 2009-08-05 00:00:00.0003 vvvvvv 公司1 2009-08-05 00:00:00.0004 cccccc 公司2 2009-08-05 00:00:00.0005 dddddd 公司2 2009-08-05 00:00:00.000NULL NULL NULL NULL6 eeeeee 公司2 2009-08-05 00:00:00.0007 ffffff 公司3 2009-08-05 00:00:00.0008 gggggg 公司3 2009-08-05 00:00:00.0009 hhhhhh 公司3 2009-08-05 00:00:00.000NULL NULL NULL NULL(11 行受影响)*/
[解决办法]
--> SQL2000:If not object_id('[tb]') is null Drop table [tb]GoCreate table [tb]([ID] int,[P_TITLE] nvarchar(6),[P_COMPANY] nvarchar(3),[P_TIME] Datetime)Insert tbSelect 1,'aaaaaa','公司1','2009-08-05' union allSelect 2,'bbbbbb','公司1','2009-08-05' union allSelect 3,'vvvvvv','公司1','2009-08-05' union allSelect 4,'cccccc','公司2','2009-08-05' union allSelect 5,'dddddd','公司2','2009-08-05' union allSelect 6,'eeeeee','公司2','2009-08-05' union allSelect 7,'ffffff','公司3','2009-08-05' union allSelect 8,'gggggg','公司3','2009-08-05' union allSelect 9,'hhhhhh','公司3','2009-08-05'Go--Select * from tb-->SQL2000查询如下:select [ID],[P_TITLE],[P_COMPANY],[P_TIME] from ( select rn=(select count(1) from tb where id<t.id)/5,*,px=0 from tb t union all select distinct (select count(1) from tb where id<t.id)/5,null,null,null,null,1 from tb t) as torder by rn,px/*ID P_TITLE P_COMPANY P_TIME----------- ------- --------- -----------------------1 aaaaaa 公司1 2009-08-05 00:00:00.0002 bbbbbb 公司1 2009-08-05 00:00:00.0003 vvvvvv 公司1 2009-08-05 00:00:00.0004 cccccc 公司2 2009-08-05 00:00:00.0005 dddddd 公司2 2009-08-05 00:00:00.000NULL NULL NULL NULL6 eeeeee 公司2 2009-08-05 00:00:00.0007 ffffff 公司3 2009-08-05 00:00:00.0008 gggggg 公司3 2009-08-05 00:00:00.0009 hhhhhh 公司3 2009-08-05 00:00:00.000NULL NULL NULL NULL(11 行受影响)*/
[解决办法]
---------------------------------
-- Author: HEROWANG(让你望见影子的墙)
-- Date : 2009-08-06 08:22:30
---------------------------------
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
go
CREATE TABLE tb (id INT,name VARCHAR(2))
INSERT INTO tb
SELECT 1,'a1' UNION ALL
SELECT 2,'a2' UNION ALL
SELECT 3,'a3' UNION ALL
SELECT 4,'a4' UNION ALL
SELECT 5,'a5' UNION ALL
SELECT 6,'b1' UNION ALL
SELECT 7,'b2' UNION ALL
SELECT 8,'b3' UNION ALL
SELECT 9,'b4' UNION ALL
SELECT 10,'b5' UNION ALL
SELECT 11,'c1' UNION ALL
SELECT 12,'c2' UNION ALL
SELECT 13,'c3' UNION ALL
SELECT 14,'c4' UNION ALL
SELECT 15,'c5'
;
with
wang as
(select cast(id as varchar) id,name , id as order1,0 as order2 from tb
union all
select '',' ',id,1 from tb
where id%5=0
)
select id,name from wang
order by order1
idname
1a1
2a2
3a3
4a4
5a5
6b1
7b2
8b3
9b4
10b5
11c1
12c2
13c3
14c4
15c5
--> SQL2000:If not object_id('[tb]') is null Drop table [tb]GoCreate table [tb]([ID] int,[P_TITLE] nvarchar(6),[P_COMPANY] nvarchar(3),[P_TIME] Datetime)Insert tbSelect 1,'aaaaaa','公司1','2009-08-05' union allSelect 2,'bbbbbb','公司1','2009-08-05' union allSelect 3,'vvvvvv','公司1','2009-08-05' union allSelect 5,'cccccc','公司2','2009-08-05' union allSelect 6,'dddddd','公司2','2009-08-05' union allSelect 8,'eeeeee','公司2','2009-08-05' union allSelect 9,'ffffff','公司3','2009-08-05' union allSelect 10,'gggggg','公司3','2009-08-05' union allSelect 12,'hhhhhh','公司3','2009-08-05'Go--Select * from tb-->SQL2000查询如下:select [ID],[P_TITLE],[P_COMPANY],[P_TIME] from ( select rn=(select count(1) from tb where id<t.id)/5,*,px=0 from tb t union all select distinct (select count(1) from tb where id<t.id)/5,null,null,null,null,1 from tb t) as torder by rn,px/*ID P_TITLE P_COMPANY P_TIME ----------- ------- --------- ------------------------------------------------------ 1 aaaaaa 公司1 2009-08-05 00:00:00.0002 bbbbbb 公司1 2009-08-05 00:00:00.0003 vvvvvv 公司1 2009-08-05 00:00:00.0005 cccccc 公司2 2009-08-05 00:00:00.0006 dddddd 公司2 2009-08-05 00:00:00.000NULL NULL NULL NULL8 eeeeee 公司2 2009-08-05 00:00:00.0009 ffffff 公司3 2009-08-05 00:00:00.00010 gggggg 公司3 2009-08-05 00:00:00.00012 hhhhhh 公司3 2009-08-05 00:00:00.000NULL NULL NULL NULL（所影响的行数为 11 行）*/
[解决办法]
顶，楼上高手
[解决办法]
If not object_id('[tb]') is null Drop table [tb]GoCreate table [tb]([ID] int,[P_TITLE] nvarchar(30),[P_COMPANY] nvarchar(50),[P_TIME] Datetime)Insert tbSelect 1,'数据库','ORACLE_1','2009-08-06' union allSelect 2,'数据库','SQL SERVER 2000','2009-08-06' union allSelect 3,'数据库','SQL SERVER 2005','2009-08-06' union allSelect 7,'数据库','SQL SERVER 2008','2009-08-06' union allSelect 8,'操作系统','MS DOS','2009-08-06' union allSelect 9,'操作系统','WINDOWS 95','2009-08-06' union allSelect 12,'操作系统','WINDOWS 98','2009-08-06' union allSelect 14,'操作系统','WINDOWS 2000','2009-08-06' union allSelect 15,'操作系统','WINDOWS 2000 SERVER','2009-08-06' union allSelect 16,'操作系统','WINDOWS XP PRO','2009-08-06' union allSelect 17,'操作系统','WINDOWS 2003 SERVER ent','2009-08-06' union allSelect 18,'操作系统','WINDOWS 2008 ENT','2009-08-06' union allSelect 19,'列车','昆明-成都2003次','2009-08-06' union allSelect 20,'列车','昆明-成都2640次','2009-08-06' union allSelect 21,'列车','昆明-成都2650次','2009-08-06' union allSelect 23,'列车','昆明-成都K938次','2009-08-06' union allSelect 25,'列车','昆明-北京t62次','2009-08-06' goselect [ID],[P_TITLE],[P_COMPANY],[P_TIME] from ( select rn=(select count(1) from tb where id<t.id)/5,*,px=0 from tb t union all select distinct (select count(1) from tb where id<t.id)/5,null,null,null,null,1 from tb t) as torder by rn,px
[解决办法]
支持高手们select [ID],[P_TITLE],[P_COMPANY],[P_TIME] from ( select rn=(select count(1) from tb where id<t.id)/5,*,px=0 --每5行显示在一起 from tb t union all select distinct (select count(1) from tb where id<t.id)/5,null,null,null,null,1 --5条记录去重复后为一条 from tb t) as torder by rn,px
[解决办法]
关注中......
[解决办法]
学习
[解决办法]
学习学习。。。
[解决办法]
看明白了
[解决办法]
不知道
[解决办法]
DECLARE @TB TABLE([ID] INT)INSERT @TB SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5;WITH C AS(SELECT *,SEQ=ROW_NUMBER() OVER (ORDER BY GETDATE()) FROM @TB )SELECT CASE WHEN ID=6 THEN '' ELSE RTRIM(ID) END AS IDFROM (SELECT *,ID AS ID2 FROM CUNION ALLSELECT ID+1,SEQ,ID+1 FROM C WHERE ID=5) TORDER BY (SEQ-1)/5,ID2
[解决办法]
厉害，都是高手啊。不知道为什么有这需求，SQL编程游戏？游戏而矣。
以上纯属个人观点，如有冒犯，还望海涵。
[解决办法]
可能是为了方便看吧！
[解决办法]
学习。。。
[解决办法]
学习。。。
[解决办法]
学习。。。
[解决办法]
学习。。。
------解决方案--------------------
高手啊，佩服~~~~
当初为了实现这个功能，又是临时表又是游标的折腾死了我.....
[解决办法]
前台判断，建个标记，每五加个div
[解决办法]
学习!
[解决办法]
在前台去做，每5行，加一个<DIV>就可以了。
[解决办法]
不明白
[解决办法]
牛人不少啊
[解决办法]
牛得很啊
[解决办法]
大涨见识了。.
[解决办法]
好东西啊
[解决办法]
实用，帮顶，＾＾
[解决办法]
其实也就是一排序而已。将空行排成每5条记录一个。
像这种排版完全可以让程序区处理，效率也高些，也方便些。。。
为什么有些人总是让SQL做些它不擅长的事情呢？
[解决办法]
如果，不管实现的过程，可以在前台用分页显示 没五行一页
[解决办法]
如果是oracle就好了 ！ 直接就 set linesize 50;set pagesize 5;
[解决办法]
楼主求得不是sql，而是寂寞
[解决办法]
没有很明白
[解决办法]
交由前台分页等.
[解决办法]
写个函数，根据序号搞定
[解决办法]
挣点积分，顶一下，呵呵。
[解决办法]
Mark
[解决办法]
不少牛人啊