sql server 循环遍历表数据

-- 方法3：使用临时表
-- 创建临时表

--SELECT empid,firstname,lastname 
--INTO dbo.#tempemployees
--FROM HR.Employees
--ORDER BY empid;

--SELECT * FROM dbo.#tempemployees;

drop table #temp
drop table #tempTwo
 create TABLE #temp 
  (
      id INT,
      cno NVARCHAR(10),
      note NVARCHAR(20),
      content nvarchar(max)
  );
   create TABLE #tempTwo 
  (
      id INT,
      cno NVARCHAR(10),
      note NVARCHAR(20),
      content nvarchar(max)
  );
  insert into #temp
  select id,QId,'',Content from LZExam.dbo.tb_ChooseQuestionOption
    
-- 声明变量
DECLARE
    @empid AS INT,
    @firstname AS NVARCHAR(10),
    @lastname AS NVARCHAR(20),
    @newQid as int,
    @Qid AS INT,
    @qcount as int,
    @id as nvarchar(20),
    @content nvarchar(max)
    set @qcount=0;
    set @newQid=1;
WHILE EXISTS(select id,cno,note,content from #temp)
BEGIN
    -- 也可以使用top 1
    SET ROWCOUNT 1
    
    select @id=id,@Qid=cno,@content=content from #temp
    
    print @qcount
    print @id
    print @Qid
    if(@Qid=@newQid)
    begin
      set @qcount+=1;
    insert into #tempTwo values(@id,@Qid,@qcount,@content)
    
    end
    else
    begin
    set @newQid=@Qid;
    set @qcount=1;
    insert into #tempTwo values(@id,@Qid,@qcount,@content)
    end
    SET ROWCOUNT 0
    
   DELETE FROM #temp WHERE id=@id;
END
--select * from tb_ChooseQuestionOption
--select * from #temp
--lianxing@admin
update #tempTwo set note= REPLACE(REPLACE(REPLACE(replace(note,'1','A'),'2','B'),'3','C'),'4','D')
select * from #tempTwo
select * from LZExam.dbo.tb_ChooseQuestionOption
select * from LZExam.dbo.tb_ChooseQuestionOption2

insert into LZExam.dbo.tb_ChooseQuestionOption2
select cno as QId, content as Content,note as Content2,id as Id2 from #tempTwo

select * from #tempTree