SQL临时表递归查询子信息并返回记录

 declare @Q_ID uniqueidentifier
 set @Q_ID = dbo.uf_GetParamValueByName(@Params,'@指标ID');
 declare @QAExp_ID char(36)  --指标属性公式ID
 set @QAExp_ID='3D2B8F3F-0B7E-46FD-9B33-050F846C2869'
 declare @temp_qid table(QID  char(36),ExpValue nvarchar(max)) --临时表变量获得指标根ID
 declare @QIDtemp char(36),@express nvarchar(4000)
 declare @k int=2        --层次
 declare @pattern nvarchar(2)='ID'  --指标公式拆分字段
 declare @charidex int       --指标对应的索引
 if(@OPType = '根据指标ID查找公式所有子指标')
   begin 
  create table #TempQuotaStruct  --创建临时表#TmpStruct
  (
   QID  char(36),      --创建一个ID用来存储指标ID
   PID  char(36),      --用来存储该指标相关的iD
   OrderValue int        --层级关系
  )
  insert #TempQuotaStruct(QID,OrderValue)values(@Q_ID,1)
  
  while EXISTS(select Q_ID from EOTS_QuotaAttributeValue where QA_ID=@QAExp_ID and Q_ID in (select QID from #TempQuotaStruct where OrderValue=@k-1)  )
   begin
    insert into @temp_qid  select Q_ID,QAV_Value from EOTS_QuotaAttributeValue whereQA_ID=@QAExp_ID and Q_ID in (select QID from #TempQuotaStruct whereOrderValue=@k-1)
    WHILE EXISTS(select QID from @temp_qid)
     begin
      select top 1 @QIDtemp=QID, @express=ExpValue from @temp_qid
      print @QIDtemp
      set  @express=rtrim(ltrim(@express))
      set  @charidex=charindex(@pattern,@express)
      while @charidex>=1
       begin
        insert into #TempQuotaStruct(QID,PID,OrderValue)values(SUBSTRING(@express,@charidex+2,36),@QIDtemp,@k)
        set    @express=SUBSTRING(@express,@charidex+38,len(@express)-@charidex+37)
        set    @charidex=charindex(@pattern,@express)
       end
      delete from @temp_qid where QID = @QIDtemp
      
     end
    set @k=@k+1
   end
  select a.*,b.Q_Name, c.QAV_Value as Q_Formula from #TempQuotaStruct a,EOTS_Quota b,EOTS_QuotaAttributeValue c where a.QID=b.Q_ID and a.QID=c.Q_ID and c.QA_ID='3D2B8F3F-0B7E-46FD-9B33-050F846C2869' 

 

 

此SQL是对标模块的临时表查询，他能查出一个指标下面的多个子指标的公式，并分层级显示 @指标ID=#*1*#;#*1*#08bea0aaf-0ed2-4c9b-8c20-8c5bd919db6f#*1*#

摘自EOTS 存储过程up_eots_Get_QuotaStruct

临时表循环的关键就是下面的语句, select top 1 from #Tep,用一个变量循环取值，然后删除循环中的值

insert into #Tep select optname,value,major_version from MSreplication_options
while Exists(select a from #Tep)
 begin
   select top 1 @tempa =a,@tempc =c from #Tep

   delete #Tep where a=@tempa
 end