字符函数实例与应用实例,SQL临时表递归查询子信息并返回记录的代码

复制代码 代码如下: declare @Q_ID
uniqueidentifier set @Q_ID =
dbo.uf_北京pk赛车官网下载,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′
此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

sql 2005 字符函数实例与应用实例

use demo go /* 将表code的列string中的值提取放到record表中 string
中字符类型为 dsddddd,2222222,222221,3
其中最后一位为标记对于record表中的biaoji
前面的以’,’分割的是值对应record表中value */ go drop proc
proc_split_code go create proc proc_split_code as begin set nocount
on

declare @count int –条数 declare @index int –变量 set @index = 1
–默认 select @count = count(*) from code –print @count while
(@index=@count) begin declare @biaoji int — 标记 declare @string
nvarchar(1000)–字符串 declare @temp int –分隔符的位置 declare @star
int –开始位置 declare @code nvarchar(100) — set @star = 0 select
@string=reverse(string) from ( select row_number() over(order by
string) as rownumber,* from code ) as a where rownumber between @index
and @index

set @temp=charindex(‘,’,@string,@star) set @biaoji =
substring(@string,@star,@temp) print @biaoji set @string =
reverse(@string) set @temp=charindex(‘,’,@string,@star) set @star = 0
while(@temp0) begin

set @temp=charindex(‘,’,@string,@star)

–print @star –print @temp

if @temp 0 begin set @code=substring(@string,@star,@temp-@star) print
@code –插入到相应的表中 insert into record(biaoji,value,time) values
(@biaoji,@code,getdate())

end set @star=@temp+1 end

–print @index print @string set @index = @index+1 end end go

exec proc_split_code

发表评论

电子邮件地址不会被公开。 必填项已用*标注