34,593
社区成员
发帖
与我相关
我的任务
分享
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[f_split](@c varchar(5000),@split varchar(2))
returns @t table(col varchar(5000))
as
begin
declare @tmpstr varchar
declare @index int
set @index=1;
while(charindex(@split,@c,@index)<>0)
begin
set @tmpstr=substring(@c,charindex(@split,@c,@index),charindex(@split,@c,@index))
if(ascii(@tmpstr)<>163)
begin
insert @t(col) values (substring(@c,1,charindex(@split,@c,@index)-1))
set @c = stuff(@c,1,charindex(@split,@c,@index),'')
set @index=1
end
else
begin
set @index=(charindex(@split,@c,@index)+1)
end
end
insert @t(col) values (@c)
return
end
CREATE TABLE #PhantomFormula(
[ID] [int] IDENTITY(1,1) NOT NULL,
[MeterNo] [nvarchar](50) NULL ,
[Formula][nvarchar](50) NULL
)
CREATE TABLE #Meter(
[ID] [int] IDENTITY(1,1) NOT NULL,
[MeterNo][nvarchar](50) NULL ,
[RtQty] decimal(12,2) NULL
)
insert into #Meter
select '1',111.00 union all
select '2',222.00 union all
select '3',333.00 union all
select '4',444.00 union all
select '5',555.00 union all
select '6',666.00
insert into #PhantomFormula
select 'XB0301','XB0301=1+2-3' union all
select 'XB0302','XB0302=2+3+4' union all
select 'XB0303','XB0303=3+4-5' union all
select 'XB0304','XB0304=4+5+6'
declare @id int
set @id=3 ----当前要查询的 ID
----取表达式等号右边数据
;with cte_a as(
select ID,MeterNO, right(Formula, charindex('=',reverse(Formula))-1) Formula from #PhantomFormula where Id=@id
)
----将+、-替换为"+","-"
,cte_b as(
select ID,MeterNO, replace(replace(Formula,'+','"+"'),'-','"-"') Formula from cte_a
)
----将表达式split
,cte_c as(
select @id as id, * from dbo.f_split((select Formula from cte_b ),'"')
)
---按表达式数字查询 表“Meter”的数据,替换掉
,cte_d as(
select id,
case when col<>'+' and col<>'-'
then
(select cast(RtQty as nvarchar(100)) from #Meter where MeterNo=col)
else
col
end col
from cte_c
)
----查询最终结果
select t.id,MeterNO, replace(stuff((select '_'+cast (col as nvarchar(100)) from cte_d z where t.id=z.id for xml path('')), 1, 1, ''),'_','' ) as result
from cte_d t
join #PhantomFormula a on t.id=a.id
group by t.id ,a.MeterNO
drop table #PhantomFormula
drop table #Meter
--------------------------------
id MeterNO result
3 XB0303 333.00+444.00-555.00
----------------------------------
(6 行受影响)
(4 行受影响)
(1 行受影响)