如何用SQL语句完成五行为一组数据的划分？

RICHEER COCA 2015-05-12 11:08:29
在数据表tb1里，字段data有20万行数据，例如


12

23

34

33

44

321

456

444

778

787

456

12365

.AA

DD

GG

HH

JKL

.....

如何用SQL语句完成五行为一组数据的划分？就是五行加一行空的，以此类推，谢谢指教


12

23

34

33

44



321

456

444

778

787



456

12365

.AA

DD

GG



HH

JKL

.....

...全文
722 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
RICHEER COCA 2015-05-14
  • 打赏
  • 举报
 回复
感谢 3 楼大师 ky_min 的指点，一步到位，言之有理，谢谢！ 感谢 4 楼大师wmxcn2000 卖水果的net的指教，学习了另一种方法，受益匪浅，一并谢谢！结贴
卖水果的net 2015-05-13
  • 打赏
  • 举报
 回复 

-- 这个需求，很是奇怪 
-- 来的早，给你写一个。

create table test(name varchar(10))
go
insert into test values
('12'),
('23'),
('34'),
('33'),
('44'),
('321'),
('456'),
('444'),
('778'),
('787'),
('456'),
('12365'),
('.AA'),
('DD'),
('GG'),
('HH'),
('JKL')
go
--select * from test 
go
with m as 
(
select ROW_NUMBER() over(order by getdate()) rn , * from test 
) ,
x as (select 1 rn  union all select 2 ) 
select case x.rn when 1 then m.name else '' end 
from m , x where x.rn = 1 or x.rn = 2 and m.rn % 5 = 0 
go
drop table test 
go


(17 行受影响)

----------
12
23
34
33
44

321
456
444
778
787

456
12365
.AA
DD
GG

HH
JKL

(20 行受影响)
还在加载中灬 2015-05-13
  • 打赏
  • 举报
 回复
这个确实应该在显示的时候去处理，我想不出有哪不方便 
;WITH CTE AS(
	SELECT ROW_NUMBER()OVER(ORDER BY GETDATE())RN
		,*
	FROM 
	/*(--反注释该句，可以开启模拟数据
		SELECT'12' COL
		UNION ALL SELECT'23'
		UNION ALL SELECT'34'
		UNION ALL SELECT'33'
		UNION ALL SELECT'44'
		UNION ALL SELECT'321'
		UNION ALL SELECT'456'
		UNION ALL SELECT'444'
		UNION ALL SELECT'778'
		UNION ALL SELECT'787'
		UNION ALL SELECT'456'
		UNION ALL SELECT'12365'
		UNION ALL SELECT'.AA'
		UNION ALL SELECT'DD'
		UNION ALL SELECT'GG'
		UNION ALL SELECT'HH'
		UNION ALL SELECT'JKL'
	)--*/
	TB
)
SELECT ISNULL(LTRIM(T2.COL),'')
FROM master..spt_values T1
	LEFT JOIN CTE T2 ON T1.number=T2.RN+(T2.RN-1)/5-1
WHERE T1.type='P' AND T1.number<=(SELECT MAX(RN+(RN-1)/5-1)FROM CTE)
czfxwpy 2015-05-13
  • 打赏
  • 举报
 回复
显示层的东西
还在加载中灬 2015-05-13
  • 打赏
  • 举报
 回复
强悍~~ 不过，楼主，说真的，最好的方式，是数据库里面编号一下就好，到前端显示的时候，在做空行显示处理，这样性能要好些
卖水果的net 2015-05-13
  • 打赏
  • 举报
 回复 

-- 加一个 order by 语句就 OK 了。
-- 
if object_id('tempdb..#tb1','U') is not null drop table #tb1
go 
create table #tb1(name varchar(14))  --select * from  #tb1
go
insert into #tb1 --select SUBSTRING(notext,1,14)  from DLTou..[DLT15054]
SELECT'12' 
        UNION ALL SELECT'23'
        UNION ALL SELECT'34'
        UNION ALL SELECT'33'
        UNION ALL SELECT'44'
        UNION ALL SELECT'321'
        UNION ALL SELECT'456'
        UNION ALL SELECT'444'
        UNION ALL SELECT'778'
        UNION ALL SELECT'787'
        UNION ALL SELECT'456'
        UNION ALL SELECT'12365'
        UNION ALL SELECT'.AA'
        UNION ALL SELECT'DD'
        UNION ALL SELECT'GG'
        UNION ALL SELECT'HH'
        UNION ALL SELECT'JKL'
        UNION ALL SELECT'34'
        UNION ALL SELECT'33'
        UNION ALL SELECT'44'
        UNION ALL SELECT'321'
        UNION ALL SELECT'456'
        UNION ALL SELECT'444'
        UNION ALL SELECT'778'
        UNION ALL SELECT'787'
        UNION ALL SELECT'456'
        UNION ALL SELECT'12365'
        UNION ALL SELECT'.AA'
        UNION ALL SELECT'DD'
        UNION ALL SELECT'GG'
        UNION ALL SELECT'HH'
        UNION ALL SELECT'JKL'
         UNION ALL SELECT'34'
        UNION ALL SELECT'33'
        UNION ALL SELECT'44'
        UNION ALL SELECT'321'
        UNION ALL SELECT'456'
        UNION ALL SELECT'444'
        UNION ALL SELECT'778'
        UNION ALL SELECT'787'
        UNION ALL SELECT'456'
        UNION ALL SELECT'12365'
        UNION ALL SELECT'.AA'
        UNION ALL SELECT'DD'
        UNION ALL SELECT'GG'
        UNION ALL SELECT'HH'
        UNION ALL SELECT'JKL'
         UNION ALL SELECT'34'
        UNION ALL SELECT'33'
        UNION ALL SELECT'44'
        UNION ALL SELECT'321'
        UNION ALL SELECT'456'
        UNION ALL SELECT'444'
        UNION ALL SELECT'778'
        UNION ALL SELECT'787'
        UNION ALL SELECT'456'
        UNION ALL SELECT'12365'
        UNION ALL SELECT'.AA'
        UNION ALL SELECT'DD'
        UNION ALL SELECT'GG'
        UNION ALL SELECT'HH'
        UNION ALL SELECT'JKL'
         UNION ALL SELECT'34'
        UNION ALL SELECT'33'
        UNION ALL SELECT'44'
        UNION ALL SELECT'321'
        UNION ALL SELECT'456'
        UNION ALL SELECT'444'
        UNION ALL SELECT'778'
        UNION ALL SELECT'787'
        UNION ALL SELECT'456'
        UNION ALL SELECT'12365'
        UNION ALL SELECT'.AA'
        UNION ALL SELECT'DD'
        UNION ALL SELECT'GG'
        UNION ALL SELECT'HH'
        UNION ALL SELECT'JKL'
         UNION ALL SELECT'34'
        UNION ALL SELECT'33'
        UNION ALL SELECT'44'
        UNION ALL SELECT'321'
        UNION ALL SELECT'456'
        UNION ALL SELECT'444'
        UNION ALL SELECT'778'
        UNION ALL SELECT'787'
        UNION ALL SELECT'456'
        UNION ALL SELECT'12365'
        UNION ALL SELECT'.AA'
        UNION ALL SELECT'DD'
        UNION ALL SELECT'GG'
        UNION ALL SELECT'HH'
        UNION ALL SELECT'JKL'
         UNION ALL SELECT'34'
        UNION ALL SELECT'33'
        UNION ALL SELECT'44'
        UNION ALL SELECT'321'
        UNION ALL SELECT'456'
        UNION ALL SELECT'444'
        UNION ALL SELECT'778'
        UNION ALL SELECT'787'
        UNION ALL SELECT'456'
        UNION ALL SELECT'12365'
        UNION ALL SELECT'.AA'
        UNION ALL SELECT'DD'
        UNION ALL SELECT'GG'
        UNION ALL SELECT'HH'
        UNION ALL SELECT'JKL'
 
create table test(name varchar(14))
go
insert into test 
 select * from  #tb1
go
--select * from test 
go
with m as 
(
select ROW_NUMBER() over(order by getdate()) rn , * from test 
) ,
x as (select 1 rn  union all select 2 ) 
select case x.rn when 1 then m.name else '' end , m.rn , x.rn
from m , x where x.rn = 1 or x.rn = 2 and m.rn % 5 = 0 
order by m.rn , x.rn  -- 加上这句就可以了
go
drop table test 
go


(107 行受影响)

(107 行受影响)
               rn                   rn
-------------- -------------------- -----------
12             1                    1
23             2                    1
34             3                    1
33             4                    1
44             5                    1
               5                    2
321            6                    1
456            7                    1
444            8                    1
778            9                    1
787            10                   1
               10                   2
456            11                   1
12365          12                   1
.AA            13                   1
DD             14                   1
GG             15                   1
               15                   2
HH             16                   1
JKL            17                   1
34             18                   1
33             19                   1
44             20                   1
               20                   2
321            21                   1
456            22                   1
444            23                   1
778            24                   1
787            25                   1
               25                   2
456            26                   1
12365          27                   1
.AA            28                   1
DD             29                   1
GG             30                   1
               30                   2
HH             31                   1
JKL            32                   1
34             33                   1
33             34                   1
44             35                   1
               35                   2
321            36                   1
456            37                   1
444            38                   1
778            39                   1
787            40                   1
               40                   2
456            41                   1
12365          42                   1
.AA            43                   1
DD             44                   1
GG             45                   1
               45                   2
HH             46                   1
JKL            47                   1
34             48                   1
33             49                   1
44             50                   1
               50                   2
321            51                   1
456            52                   1
444            53                   1
778            54                   1
787            55                   1
               55                   2
456            56                   1
12365          57                   1
.AA            58                   1
DD             59                   1
GG             60                   1
               60                   2
HH             61                   1
JKL            62                   1
34             63                   1
33             64                   1
44             65                   1
               65                   2
321            66                   1
456            67                   1
444            68                   1
778            69                   1
787            70                   1
               70                   2
456            71                   1
12365          72                   1
.AA            73                   1
DD             74                   1
GG             75                   1
               75                   2
HH             76                   1
JKL            77                   1
34             78                   1
33             79                   1
44             80                   1
               80                   2
321            81                   1
456            82                   1
444            83                   1
778            84                   1
787            85                   1
               85                   2
456            86                   1
12365          87                   1
.AA            88                   1
DD             89                   1
GG             90                   1
               90                   2
HH             91                   1
JKL            92                   1
34             93                   1
33             94                   1
44             95                   1
               95                   2
321            96                   1
456            97                   1
444            98                   1
778            99                   1
787            100                  1
               100                  2
456            101                  1
12365          102                  1
.AA            103                  1
DD             104                  1
GG             105                  1
               105                  2
HH             106                  1
JKL            107                  1

(128 行受影响)
RICHEER COCA 2015-05-13
  • 打赏
  • 举报
 回复
如果数据在100行以内，五行分隔是正确的， 问题：现在有20W行数据，一旦数据[超过101行时，就出现错误，无法每五行添加一行空行分隔，请指教，附代码，谢谢指点！
RICHEER COCA 2015-05-13
  • 打赏
  • 举报
 回复
引用 4 楼 wmxcn2000 的回复:

create table test(name varchar(10))
go
insert into test values
('12'),
('23'),
('34'),
('33'),
('44'),
('321'),
('456'),
('444'),
('778'),
('787'),
('456'),
('12365'),
('.AA'),
('DD'),
('GG'),
('HH'),
('JKL')
go
--select * from test 
go
with m as 
(
select ROW_NUMBER() over(order by getdate()) rn , * from test 
) ,
x as (select 1 rn  union all select 2 ) 
select case x.rn when 1 then m.name else '' end 
from m , x where x.rn = 1 or x.rn = 2 and m.rn % 5 = 0 
go
drop table test 
go
首先感谢4楼及楼上大师的援手及无私奉献！！！ 如果数据超过101行时，就出现错误，无法每五行添加一行空行，请指教，附代码，谢谢修订！ 
if object_id('tempdb..#tb1','U') is not null drop table #tb1
go 
create table #tb1(name varchar(14))  --select * from  #tb1
go
insert into #tb1 --select SUBSTRING(notext,1,14)  from DLTou..[DLT15054]
SELECT'12' 
        UNION ALL SELECT'23'
        UNION ALL SELECT'34'
        UNION ALL SELECT'33'
        UNION ALL SELECT'44'
        UNION ALL SELECT'321'
        UNION ALL SELECT'456'
        UNION ALL SELECT'444'
        UNION ALL SELECT'778'
        UNION ALL SELECT'787'
        UNION ALL SELECT'456'
        UNION ALL SELECT'12365'
        UNION ALL SELECT'.AA'
        UNION ALL SELECT'DD'
        UNION ALL SELECT'GG'
        UNION ALL SELECT'HH'
        UNION ALL SELECT'JKL'
        UNION ALL SELECT'34'
        UNION ALL SELECT'33'
        UNION ALL SELECT'44'
        UNION ALL SELECT'321'
        UNION ALL SELECT'456'
        UNION ALL SELECT'444'
        UNION ALL SELECT'778'
        UNION ALL SELECT'787'
        UNION ALL SELECT'456'
        UNION ALL SELECT'12365'
        UNION ALL SELECT'.AA'
        UNION ALL SELECT'DD'
        UNION ALL SELECT'GG'
        UNION ALL SELECT'HH'
        UNION ALL SELECT'JKL'
         UNION ALL SELECT'34'
        UNION ALL SELECT'33'
        UNION ALL SELECT'44'
        UNION ALL SELECT'321'
        UNION ALL SELECT'456'
        UNION ALL SELECT'444'
        UNION ALL SELECT'778'
        UNION ALL SELECT'787'
        UNION ALL SELECT'456'
        UNION ALL SELECT'12365'
        UNION ALL SELECT'.AA'
        UNION ALL SELECT'DD'
        UNION ALL SELECT'GG'
        UNION ALL SELECT'HH'
        UNION ALL SELECT'JKL'
         UNION ALL SELECT'34'
        UNION ALL SELECT'33'
        UNION ALL SELECT'44'
        UNION ALL SELECT'321'
        UNION ALL SELECT'456'
        UNION ALL SELECT'444'
        UNION ALL SELECT'778'
        UNION ALL SELECT'787'
        UNION ALL SELECT'456'
        UNION ALL SELECT'12365'
        UNION ALL SELECT'.AA'
        UNION ALL SELECT'DD'
        UNION ALL SELECT'GG'
        UNION ALL SELECT'HH'
        UNION ALL SELECT'JKL'
         UNION ALL SELECT'34'
        UNION ALL SELECT'33'
        UNION ALL SELECT'44'
        UNION ALL SELECT'321'
        UNION ALL SELECT'456'
        UNION ALL SELECT'444'
        UNION ALL SELECT'778'
        UNION ALL SELECT'787'
        UNION ALL SELECT'456'
        UNION ALL SELECT'12365'
        UNION ALL SELECT'.AA'
        UNION ALL SELECT'DD'
        UNION ALL SELECT'GG'
        UNION ALL SELECT'HH'
        UNION ALL SELECT'JKL'
         UNION ALL SELECT'34'
        UNION ALL SELECT'33'
        UNION ALL SELECT'44'
        UNION ALL SELECT'321'
        UNION ALL SELECT'456'
        UNION ALL SELECT'444'
        UNION ALL SELECT'778'
        UNION ALL SELECT'787'
        UNION ALL SELECT'456'
        UNION ALL SELECT'12365'
        UNION ALL SELECT'.AA'
        UNION ALL SELECT'DD'
        UNION ALL SELECT'GG'
        UNION ALL SELECT'HH'
        UNION ALL SELECT'JKL'
         UNION ALL SELECT'34'
        UNION ALL SELECT'33'
        UNION ALL SELECT'44'
        UNION ALL SELECT'321'
        UNION ALL SELECT'456'
        UNION ALL SELECT'444'
        UNION ALL SELECT'778'
        UNION ALL SELECT'787'
        UNION ALL SELECT'456'
        UNION ALL SELECT'12365'
        UNION ALL SELECT'.AA'
        UNION ALL SELECT'DD'
        UNION ALL SELECT'GG'
        UNION ALL SELECT'HH'
        UNION ALL SELECT'JKL'

create table test(name varchar(14))
go
insert into test 
 select * from  #tb1
go
--select * from test 
go
with m as 
(
select ROW_NUMBER() over(order by getdate()) rn , * from test 
) ,
x as (select 1 rn  union all select 2 ) 
select case x.rn when 1 then m.name else '' end 
from m , x where x.rn = 1 or x.rn = 2 and m.rn % 5 = 0 
go
drop table test 
go
tcmakebest 2015-05-13
  • 打赏
  • 举报
 回复
赶鸭子上架啊,这显示层的需求让数据层解决,太不合理了. 一次要取20万条,太恐怖了.
基本SQL语句(一篇就够了)
基本SQL语句(一篇就够了) SQL(Structured Query Language)结构化查询语言，用于存取，查询，更新数据以及管理关系型数据库系统 SQL是1981年由IBM公司推出 SQL分类 根据SQL指令完成的数据库操作的不同，可以将SQL指令分为四类 DDL Data Defintion language 数据库定义语言 用于完成对数据库对象(数据表,数据库,视图,索引)的创建，删除，修改 DML Data Manipulation language 数据操作语言 用于完成对
自动检测SQL语句的神器？再也不用担心SQL出错了！
Yearning MYSQL 是一个SQL语句审核平台。提供查询审计，SQL审核等多种功能，支持Mysql，可以在一定程度上解决运维与开发之间的那一环，功能丰富，代码开源，安装部署容易！1、SQL查询查询导出查询自动补全2、SQL审核流程化工单SQL语句检测SQL语句执行SQL回滚3、历史审核记录4、查询审计5、推送E-mail工单推送钉钉webhook机器人工单推送6、其他LDAP登陆用户权限及管理拼图式细粒度权限划分(共12项独立权限,可随意组合)Dashboarddashboard主要展示Yearni
数据库备份与SQL语句
文章目录数据库备份数据库常用备份方案mysql备份工具mysqldump差异备份与恢复SQL语句group by 使用自然连接INNER JOIN联合连接（UNION JOIN）外连接 数据库备份 数据库常用备份方案 全量备份 增量备份 差异备份 备份方案 特点 全量备份 全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。 数据恢复快。 备份时间长 增量备份 增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份 与前一次相比增加和者被修改的文件。这就意味
SQL语句执行优先级
SQL语句执行优先级 要知道sql语句，我想我们有必要知道sqlserver查询分析器怎么执行我么sql语句的，我么很多人会看执行计划，或者用profile来监视和调优查询语句或者存储过程慢的原因，但是如果我们知道查询分析器的执行逻辑顺序，下手的时候就胸有成竹，那么下手是不是有把握点呢？ 一、查询的逻辑执行顺序 (1) FROM < left_table> (3) < join...
SQL语言的组成分为哪几个部分
 详细来讲，可以分为六个部分一：数据查询语言（DQL:Data Query Language）：　　其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。保留字SELECT是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有WHERE，ORDER BY，GROUP BY和HAVING。这些DQL保留字常与其他类型的SQL语句一起使用。　二：数据操作语言（DM...
疑难问题

22,209

社区成员

121,731

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章