22,209
社区成员
发帖
与我相关
我的任务
分享
if object_id('tempdb.dbo.#temp') is not null drop table #temp
go
create table #temp([学号] nvarchar(14))
insert #temp
select 2578451 union all
select 2578445 union all
select 2578447 union all
select 2578443 union all
select 2578442 union all
select 2578454 union all
select 2578453 union all
select 2578444 union all
select 2578446 union all
select 2578455 union all
select 2578456 union all
select 2578449 union all
select 2578452 union all
select 2578450 union all
select 2578448
--------------生成数据--------------- select * from #temp
--> 测试数据 结果有误,如果数据表的数据是1000行数据,测试结果是对的,如果数据有2571行,但测试结果仅有2408行,请帮忙修改代码
--楼主代码
;WITH CTE AS(
SELECT ROW_NUMBER()OVER(ORDER BY GETDATE())RN
,[学号]
FROM #temp
)
SELECT ISNULL(LTRIM(T2.[学号]),'')
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) -----希望是5行添加一行空行的结果
drop table #temp
IF OBJECT_ID('master..spt_values_max','U') IS NOT NULL
DROP TABLE master..spt_values_max
GO
CREATE TABLE master..spt_values_max(
id BIGINT
)
GO
DECLARE @i bigINT,@iMax bigINT
SET @i=1
SET @iMax=10000 --不够自己加
WHILE @i<=@iMax
BEGIN
INSERT INTO master..spt_values_max (id)
SELECT ISNULL((SELECT MAX(id) FROM master..spt_values_max),0)+1
SET @i=@i+1
END
;WITH CTE AS(
SELECT ROW_NUMBER()OVER(ORDER BY GETDATE()) -1 RN
,[学号]
FROM #temp
)
,Nrs
AS
(
SELECT TOP ((SELECT COUNT(1)+COUNT(1)/5 FROM Cte)) ROW_NUMBER()OVER(ORDER BY GETDATE())-1 AS Nr FROM sys.columns AS a,sys.columns AS b,sys.columns AS c,sys.columns AS d
)
SELECT ISNULL(LTRIM(T2.[学号]),'')
FROM Nrs T1
LEFT JOIN CTE T2 ON T1.Nr%6=T2.RN%5 AND T1.Nr/6=T2.RN/5
ORDER BY T1.Nr
select ROW_NUMBER() OVER(ORDER BY GETDATE()) number from master..syscolumns
IF OBJECT_ID('tempdb.dbo.#temp') IS NOT NULL DROP TABLE #temp
GO
CREATE TABLE #temp([学号] NVARCHAR(14))
INSERT #temp
SELECT 2578451 UNION ALL
SELECT 2578445 UNION ALL
SELECT 2578447 UNION ALL
SELECT 2578443 UNION ALL
SELECT 2578442 UNION ALL
SELECT 2578454 UNION ALL
SELECT 2578453 UNION ALL
SELECT 2578444 UNION ALL
SELECT 2578446 UNION ALL
SELECT 2578455 UNION ALL
SELECT 2578456 UNION ALL
SELECT 2578449 UNION ALL
SELECT 2578452 UNION ALL
SELECT 2578450 UNION ALL
SELECT 2578448
--------------生成数据--------------- select * from #temp
--> 测试数据 结果有误,如果数据表的数据是1000行数据,测试结果是对的,如果数据有2571行,但测试结果仅有2408行,请帮忙修改代码
--楼主代码
;WITH CTE AS(
SELECT ROW_NUMBER()OVER(ORDER BY GETDATE())RN
,[学号]
FROM #temp
)
,Nrs
AS
(
SELECT TOP ((SELECT COUNT(1)+COUNT(1)/5 FROM Cte)) ROW_NUMBER()OVER(ORDER BY GETDATE()) AS Nr FROM sys.columns AS a,sys.columns AS b,sys.columns AS c,sys.columns AS d
)
SELECT ISNULL(LTRIM(T2.[学号]),'')
FROM Nrs T1
LEFT JOIN CTE T2 ON (T1.Nr-1)%6+1=(T2.RN-1)%5+1 AND (T1.Nr-1)/6=(T2.RN-1)/5
ORDER BY T1.Nr
drop table #temp
/*
(无列名)
2578451
2578445
2578447
2578443
2578442
2578454
2578453
2578444
2578446
2578455
2578456
2578449
2578452
2578450
2578448
*/
[quote=引用 5 楼 roy_88 的回复:] master..spt_values--记录数不够时引起的
select number from master..spt_values where type='p'
(2048 行受影响)
select number from master..syscolumns where type='p'
消息 245,级别 16,状态 1,第 1 行 在将 varchar 值 'p' 转换成数据类型 tinyint 时失败。
master..spt_values--记录数不够时引起的
select number from master..spt_values where type='p'
(2048 行受影响)
with table1 as
(
select top 2571 (n1+1)*3 n1 from (
select a.n1*100+b.n1 n1 from
(select number n1 from master..spt_values where type='P' and number between 0 and 99) a
,(select number n1 from master..spt_values where type='P' and number between 0 and 99) b
)aaa
)
, table2 as
(
select a.n1*100 + b.n1 number from
(select number n1 from master..spt_values where type='P' and number between 0 and 99) a
,(select number n1 from master..spt_values where type='P' and number between 0 and 99) b
)
,CTE AS(
SELECT ROW_NUMBER() OVER(ORDER BY GETDATE()) RN, n1 FROM table1
)
SELECT ISNULL(LTRIM(T2.n1), '')
FROM table2 T1
LEFT JOIN CTE T2 ON T1.number = T2.RN+(T2.RN-1)/5-1
WHERE T1.number <= (SELECT MAX(RN+(RN-1)/5-1) FROM CTE)
;WITH CTE AS
(
SELECT 1 AS RN
UNION ALL
SELECT RN +1
FROM CTE
WHERE CTE.RN < 50
)
SELECT *
FROM CTE a
FULL JOIN CTE b ON 1 = 1