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

Question

如何依次分别取 表#tb1里MID=1、2、3时的数字，然后再随机取10位数一次，
表#tb1里有三行记录，每行记录 SQL代码循环执行一次，实际上应该有3行结果

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

问题 依次取 表#tb1里的数字，但结果是把三行的数字全部取了，是错误的
用 WITH T AS 取数据出现错误，请问如何纠正

Answer 1

你取 #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看得有点难受，不整了