RICHEER COCA 2022-01-03 07:58 采纳率: 93.9%

# 如何依次分别取 表#tb1里MID=1、2、3时的数字，然后再随机取10位数一次,请专家答疑解惑

``````if object_id('[tempdb]..#tb1') is not null drop table #tb1
GO
create table #tb1(
[MID] [int] IDENTITY(1,1) NOT NULL primary key,
n1 int NULL,n2 int NULL,n3 int NULL,n4 int NULL,n5 int NULL,n6 int NULL,n7 int NULL,n8 int NULL,n9 int NULL,n10 int NULL,n11 int NULL,n12 int NULL,n13 int NULL,n14 int NULL)
insert into #tb1
SELECT '1'N1,'3'N2,'5'N3,'7'N4,'9'N5,'11'N6,'13'N7,'15'N8,'17'N9,'19'N10,'21'N11,'23'N12,'25'N13,'27'N14
UNION ALL
SELECT '2','4','6','8','11','13','15','17','19','21','23','25','27','30'
UNION ALL
SELECT '12','14','16','18','11','13','15','17','19','21','23','25','27','30'

IF OBJECT_ID(N'result', N'U') IS NOT NULL
DROP TABLE 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, N7 INT,N8 INT,N9 INT, N10 INT)
DECLARE @GroupID INT=1
WHILE @GroupID < 2
BEGIN
SET @loop2 = 0;
if object_id('tempdb.dbo.#Seq') is not null drop table #Seq;

-- 问题    依次取 表#tb1里的数字，但结果是把三行的数字全部取了，是错误的
WITH T AS(
select NUMBER from #tb1 unpivot (NUMBER for TYPE in(n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,n13,n14)) as p
)

SELECT [number], NEWID() ID INTO #Seq FROM T

--循环1次，写入1行
WHILE @loop2 < 1
BEGIN
IF @loop2 < 1
SET @Nums = STUFF((   SELECT ',' + CAST(number AS VARCHAR(12))
FROM   (   SELECT   number
FROM  #Seq
ORDER BY ID OFFSET (@loop2 * 1) ROWS FETCH NEXT 10 ROWS ONLY) t
ORDER BY  t.number
FOR XML PATH('')),
1,
1,
'');
--生产写入的SQL代码
SET @sql = N'insert result (GroupID, N1, N2, N3, N4, N5, N6, N7, N8, N9, N10) VALUES (' + CAST(@GroupID AS NVARCHAR(12))
+ N',' + @Nums + N')';
--执行写入
EXEC (@sql);
SET @loop2 = @loop2 + 1;
END;
SET @GroupID = @GroupID + 1;
END;
SELECT * FROM result
``````

• 写回答

#### 1条回答默认 最新

• DarkAthena ORACLE应用及数据库设计方案咨询师 2022-01-03 14:10
关注

你取 #tb1的时候，没带where过滤条件，它当然3行记录都出来了，既然声明了@GroupID这个变量，为啥不用上去呢？

``````WITH T AS(
select NUMBER from (select * from #tb1 where MID=@GroupID) unpivot (NUMBER for TYPE in(n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,n13,n14)) as p
)

SELECT [number], NEWID() ID INTO #Seq FROM T
``````

其实大概就这个意思，子查询要不要加别名你自己去试，

我看到问题了，你order by newid()这玩意，把顺序都搞乱了

``````if object_id('[tempdb]..#tb1') is not null drop table #tb1
GO
create table #tb1(
[MID] [int] IDENTITY(1,1) NOT NULL primary key,
n1 int NULL,n2 int NULL,n3 int NULL,n4 int NULL,n5 int NULL,n6 int NULL,n7 int NULL,n8 int NULL,n9 int NULL,n10 int NULL,n11 int NULL,n12 int NULL,n13 int NULL,n14 int NULL)
insert into #tb1
SELECT '1'N1,'3'N2,'5'N3,'7'N4,'9'N5,'11'N6,'13'N7,'15'N8,'17'N9,'19'N10,'21'N11,'23'N12,'25'N13,'27'N14
UNION ALL
SELECT '2','4','6','8','11','13','15','17','19','21','23','25','27','30'
UNION ALL
SELECT '12','14','16','18','11','13','15','17','19','21','23','25','27','30'

IF OBJECT_ID(N'result', N'U') IS NOT NULL
DROP TABLE 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, N7 INT,N8 INT,N9 INT, N10 INT)
DECLARE @GroupID INT=1
WHILE @GroupID < 2
BEGIN
SET @loop2 = 0;
if object_id('tempdb.dbo.#Seq') is not null drop table #Seq;

-- 问题    依次取 表#tb1里的数字，但结果是把三行的数字全部取了，是错误的
WITH T AS(
select NUMBER,MID from #tb1 unpivot (NUMBER for TYPE in(n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,n13,n14)) as p
)

SELECT [number], MID as  ID INTO #Seq FROM T

--循环1次，写入1行
WHILE @loop2 < 1
BEGIN
IF @loop2 < 1
SET @Nums = STUFF((   SELECT ',' + CAST(number AS VARCHAR(12))
FROM   (   SELECT  id, number
FROM  #Seq
ORDER BY ID,number OFFSET (@loop2 * 10) ROWS FETCH NEXT 10 ROWS ONLY) t
ORDER BY  ID,t.number
FOR XML PATH('')),
1,
1,
'');
--生产写入的SQL代码
SET @sql = N'insert result (GroupID, N1, N2, N3, N4, N5, N6, N7, N8, N9, N10) VALUES (' + CAST(@GroupID AS NVARCHAR(12))
+ N',' + @Nums + N')';
--执行写入
EXEC (@sql);
SET @loop2 = @loop2 + 1;
END;
SET @GroupID = @GroupID + 1;
END;
SELECT * FROM result
``````

这是我第一次看sqlserver的过程代码，有点脑壳大，现在是跑第一行出来了，我再看看怎么随机

随机的出来了

``````if object_id('[tempdb]..#tb1') is not null drop table #tb1
GO
create table #tb1(
[MID] [int] IDENTITY(1,1) NOT NULL primary key,
n1 int NULL,n2 int NULL,n3 int NULL,n4 int NULL,n5 int NULL,n6 int NULL,n7 int NULL,n8 int NULL,n9 int NULL,n10 int NULL,n11 int NULL,n12 int NULL,n13 int NULL,n14 int NULL)
insert into #tb1
SELECT '1'N1,'3'N2,'5'N3,'7'N4,'9'N5,'11'N6,'13'N7,'15'N8,'17'N9,'19'N10,'21'N11,'23'N12,'25'N13,'27'N14
UNION ALL
SELECT '2','4','6','8','11','13','15','17','19','21','23','25','27','30'
UNION ALL
SELECT '12','14','16','18','11','13','15','17','19','21','23','25','27','30'

IF OBJECT_ID(N'result', N'U') IS NOT NULL
DROP TABLE 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, N7 INT,N8 INT,N9 INT, N10 INT)
DECLARE @GroupID INT=1
WHILE @GroupID < 4
BEGIN
SET @loop2 = 0;
if object_id('tempdb.dbo.#Seq') is not null drop table #Seq;

-- 问题    依次取 表#tb1里的数字，但结果是把三行的数字全部取了，是错误的
WITH T AS(
select NUMBER,MID from  #tb1  unpivot (NUMBER for TYPE in(n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,n13,n14)) as p
)

SELECT [number], MID as  ID,newid() as NID INTO #Seq FROM T

--循环1次，写入1行
WHILE @loop2 < 1
BEGIN
IF @loop2 < 1
SET @Nums = STUFF((   SELECT ',' + CAST(number AS VARCHAR(12))
FROM   (   SELECT  id, number,NID
FROM  #Seq WHERE ID=@GroupID
ORDER BY NID OFFSET (@loop2 * 1) ROWS FETCH NEXT 10 ROWS ONLY) t
ORDER BY  NID
FOR XML PATH('')),
1,
1,
'');
--生产写入的SQL代码
SET @sql = N'insert result (GroupID, N1, N2, N3, N4, N5, N6, N7, N8, N9, N10) VALUES (' + CAST(@GroupID AS NVARCHAR(12))
+ N',' + @Nums + N')';
--执行写入
EXEC (@sql);
SET @loop2 = @loop2 + 1;
END;
SET @GroupID = @GroupID + 1;
END;
SELECT * FROM result
``````

要实现你这个目的，应该有更好的方法，不过这sqlserver的sql看得有点难受，不整了

本回答被题主选为最佳回答 , 对您是否有帮助呢?
评论 编辑记录

• 系统已结题 1月12日
• 已采纳回答 1月4日
• 创建了问题 1月3日

#### 悬赏问题

• ¥15 关于#stm32#的问题：第一张图是发送数据代码，第二第三张图是接收数据并进行DAC转换的函数，若用串口一接收数据并进行DAC转换，主函数代码该怎么写
• ¥20 hc130怎么读写内部flash存储信息
• ¥15 Axure rp9注册与登录交互
• ¥15 我下载图形界面重启完就变成这样了，打字也打不了，动也动不了，该怎么解决(操作系统-centos)
• ¥15 VBA中在窗体中遍历所有checkbox控件，提取出被选中的checkbox的caption值
• ¥15 在Ubuntu上有什么命令，或者是系统文件能告诉我链接nvme ssd的pcie槽位是不是支持热插拔功能？