RICHEER COCA 2022-01-03 07:58 采纳率: 93.9%
浏览 26
已结题

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

如何依次分别取 表#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 取数据出现错误,请问如何纠正

  • 写回答

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
    

    img

    这是我第一次看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槽位是不是支持热插拔功能?
  • ¥15 ansys license许可证问题
  • ¥20 QQ号和密码都能正常登录微信 QQ号和密码登录微信显示密码错误
  • ¥15 单片机RTOS Kernel与应用分离开发,Kernel如何调起应用?
  • ¥15 快手小店商家版APP怎么第三方APP跳转到指定用户聊天界面