---随机生成一组6个号，包含33个数字，前5个各不相同，运行一次写入6行，运行2次写入12行，如何循环运行100次，把600行写入到 表 #test
SET NOCOUNT ON
IF OBJECT_ID(N'ssq2', N'U') IS NOT NULL
DROP TABLE ssq2;
GO
create table ssq2(num int,grp int)
go
declare @i int
set @i=1
while @i<=33
begin
insert into ssq2 values(@i,0)
set @i=@i+1
end
IF OBJECT_ID(N'ssq3', N'U') IS NOT NULL
DROP TABLE ssq3;
GO
select num,IDENTITY(int, 1,1) AS ID into ssq3 from ssq2 order by newid();
update a
set grp=(case when b.id<=6 then 1
when b.id<=12 and b.id>6 then 2
when b.id<=18 and b.id>12 then 3
when b.id<=24 and b.id>18 then 4
when b.id<=30 and b.id>24 then 5
when b.id<=33 and b.id>30 then 6 end)
from ssq2 a left join ssq3 b on a.num=b.num
declare @a1 int,@a2 int,@a3 int
select @a1=abs(checksum(newid())) % 33+1
while @a1 in (select num from ssq2 where grp=6)
select @a1=abs(checksum(newid())) % 33+1
insert into ssq2 values(@a1,6)
select @a2=abs(checksum(newid())) % 33+1
while @a2 in (select num from ssq2 where grp=6)
select @a2=abs(checksum(newid())) % 33+1
insert into ssq2 values(@a2,6)
select @a3=abs(checksum(newid())) % 33+1
while @a3 in (select num from ssq2 where grp=6)
select @a3=abs(checksum(newid())) % 33+1
insert into ssq2 values(@a3,6)
DROP TABLE ssq3;
select num,(select count(num) from ssq2 where grp=a.grp and num<=a.num) id,grp into ssq3 from ssq2 a
SET NOCOUNT OFF
;with t as (
select max(case when id=1 then num else 0 end) R1,
max(case when id=2 then num else 0 end) R2,
max(case when id=3 then num else 0 end) R3,
max(case when id=4 then num else 0 end) R4,
max(case when id=5 then num else 0 end) R5,
max(case when id=6 then num else 0 end) R6
from ssq3
group by grp
)
--if object_id('tempdb..#test') is not null drop table #test
--go
--create table #test(n1 int,n2 int,n3 int,n4 int,n5 int,n6 int)
insert into #test ----问题 运行一次写入6行，如何循环运行100次，把600行写入到 表 #test
select * from t
select * from #test
DROP TABLE IF EXISTS result
DECLARE @loop2 INT=0
DECLARE @Nums NVARCHAR(100)
DECLARE @sql NVARCHAR(200)=''
CREATE TABLE result (GroupID INT, N1 INT,N2 INT,N3 INT, N4 INT,N5 INT, N6 INT)
DECLARE @GroupID INT=1
WHILE @GroupID < 101
BEGIN
SET @loop2 = 0;
DROP TABLE IF EXISTS #Seq;
--生成随机排序的1~33序列
SELECT TOP (33) [number], NEWID() ID
INTO #Seq
FROM [master].[dbo].[spt_values]
WHERE type = 'p' AND number < 34 AND number > 0;
--循环6次，写入6行
WHILE @loop2 < 6
BEGIN
SET @Nums = STUFF(( SELECT ',' + CAST(number AS VARCHAR(2))
FROM #Seq
ORDER BY ID OFFSET (@loop2 * 6) ROWS FETCH NEXT 6 ROWS ONLY
FOR XML PATH('')),
1,
1,
'');
--第6次数字不够，随机找3个补上
IF @loop2 = 5
BEGIN
SET @Nums = @Nums + ( SELECT ',' + CAST(number AS VARCHAR(2))
FROM #Seq
ORDER BY NEWID()OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY
FOR XML PATH(''));
END;
--生产写入的SQL代码
SET @sql = N'insert result (GroupID, N1, N2, N3, N4, N5, N6) VALUES (' + CAST(@GroupID AS NVARCHAR(3))
+ N',' + @Nums + N')';
--执行写入
EXEC (@sql);
SET @loop2 = @loop2 + 1;
END;
SET @GroupID = @GroupID + 1;
END;
SELECT * FROM result
修改：随机补的数字不重复
DROP TABLE IF EXISTS result
DECLARE @loop2 INT=0
DECLARE @Nums NVARCHAR(100)
DECLARE @sql NVARCHAR(200)=''
CREATE TABLE result (GroupID INT, N1 INT,N2 INT,N3 INT, N4 INT,N5 INT, N6 INT)
DECLARE @GroupID INT=1
WHILE @GroupID < 101
BEGIN
SET @loop2 = 0;
DROP TABLE IF EXISTS #Seq;
--生成随机排序的1~33序列
SELECT TOP (33) [number], NEWID() ID
INTO #Seq
FROM [master].[dbo].[spt_values]
WHERE type = 'p' AND number < 34 AND number > 0;
--循环6次，写入6行
WHILE @loop2 < 6
BEGIN
IF @loop2 < 5
SET @Nums = STUFF(( SELECT ',' + CAST(number AS VARCHAR(2))
FROM ( SELECT number
FROM #Seq
ORDER BY ID OFFSET (@loop2 * 6) ROWS FETCH NEXT 6 ROWS ONLY) t
ORDER BY t.number
FOR XML PATH('')),
1,
1,
'');
--第6次数字不够，随机找3个补上
ELSE
BEGIN
SET @Nums = STUFF(
( SELECT ',' + CAST(number AS VARCHAR(2))
FROM ( SELECT number
FROM #Seq
ORDER BY ID OFFSET (@loop2 * 6) ROWS FETCH NEXT 6 ROWS ONLY
UNION ALL
SELECT number
FROM #Seq
WHERE number NOT IN ( SELECT number
FROM #Seq
ORDER BY ID OFFSET (@loop2 * 6) ROWS FETCH NEXT 6 ROWS ONLY )
ORDER BY NEWID()OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY) t
ORDER BY t.number
FOR XML PATH('')),
1,
1,
'');
END;
--生产写入的SQL代码
SET @sql = N'insert result (GroupID, N1, N2, N3, N4, N5, N6) VALUES (' + CAST(@GroupID AS NVARCHAR(3))
+ N',' + @Nums + N')';
--执行写入
EXEC (@sql);
SET @loop2 = @loop2 + 1;
END;
SET @GroupID = @GroupID + 1;
END;
SELECT * FROM result;
感谢大师的帮助，学习了，已经收藏。
WHERE number NOT IN ( SELECT number
FROM #Seq
ORDER BY ID OFFSET (@loop2 * 6) ROWS FETCH NEXT 6 ROWS ONLY )
ORDER BY NEWID()OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY) t
收藏！！！同时请教 GroupID超过20W时， 解决'将 expression 转换为数据类型 nvarchar 时出现算术溢出错误。
那么增加nvarchar类型的长度就行，改为nvarchar(8)，顺利运行通过。请问这段代码 需要修改吗？
DECLARE @Nums NVARCHAR(100)
DECLARE @sql NVARCHAR(200)=''