SQL临时表递归查询子信息并返回记录的代码
前端之家收集整理的这篇文章主要介绍了
SQL临时表递归查询子信息并返回记录的代码，
前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
<div class="codetitle"><a style="CURSOR: pointer" data="75484" class="copybut" id="copybut75484" onclick="doCopy('code75484')"> 代码如下:
<div class="codebody" id="code75484">
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_IDand 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'
