在数据表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
.....
------解决思路----------------------
这个确实应该在显示的时候去处理，我想不出有哪不方便
;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)
------解决思路----------------------
-- 加一个 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 行受影响)