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日

悬赏问题

  • ¥20 西门子S7-Graph,S7-300，梯形图
  • ¥50 用易语言http 访问不了网页
  • ¥50 safari浏览器fetch提交数据后数据丢失问题
  • ¥15 matlab不知道怎么改，求解答！！
  • ¥15 永磁直线电机的电流环pi调不出来
  • ¥15 用stata实现聚类的代码
  • ¥15 请问paddlehub能支持移动端开发吗？在Android studio上该如何部署？
  • ¥20 docker里部署springboot项目，访问不到扬声器
  • ¥15 netty整合springboot之后自动重连失效
  • ¥15 悬赏！微信开发者工具报错，求帮改