动态计算存储在表列中的表达式

7
我有以下表格。
PnlId LineTotalisationId   Designation Totalisation
    1   A   Gross Fees  Formule         A01+A02+A03+A04+A05
    2   A01 GF1         Comptes         B01+B02+B03+B04+B05
    3   A02 GF2         Comptes         C01+C02+C03+C04+C05
    4   A03 GF3         Comptes         99991
    5   A04 GF4         Comptes         99996
    6   A05 GF5         Comptes         999995
    14  B1  Perm            Comptes         12+14+25

我希望得到翻译:

如果在汇总行中,指定的职位为“公式”,则应将PnlId内容数字的总计线TotalisationId名称与Totalisation连接起来以获得总计。否则,我将只需添加该行。

我尝试了以下代码:

SELECT Ref.*, 
       CASE 
         WHEN Charindex('+', Ref.totalisation) > 0 THEN '+' 
         WHEN Charindex('/', Ref.totalisation) > 0 THEN '/' 
         WHEN Charindex('*', Ref.totalisation) > 0 THEN '*' 
         WHEN Charindex('*', Ref.totalisation) > 0 THEN '-' 
       END AS Operator 
INTO   ##ttresults 
FROM   [dbo].[pnlreference] Ref 
WHERE  [typetotalisation] = 'Formule' 
       AND totalisation <> '' 
       AND designation NOT LIKE '%[0-9]%' 
       AND designation != '' 

SELECT split.linetotalisationid AS PNLParentId, 
       NULL                     AS Totalisation 
INTO   ##tempresults 
FROM   (SELECT tbl.designation, 
               tbl.linetotalisationid, 
               tbl.typetotalisation, 
               tbl.pnlid, 
               tbl.totalisation, 
               Cast('<t>' 
                    + Replace(tbl.totalisation, tbl.operator, '</t><t>') 
                    + '</t>' AS XML) x, 
               tbl.operator 
        FROM   ##ttresults AS tbl) split 
       CROSS apply x.nodes('/t') t(c) 
       INNER JOIN [dbo].[pnlreference] Ref 
               ON Ref.linetotalisationid = t.c.value('.', 'nvarchar(255)') 
WHERE  Ref.designation LIKE '%[0-9]%' 
        OR Ref.designation = '' 
GROUP  BY split.linetotalisationid ;
上面的代码返回以下结果。
PNLParentId
A
有没有一种方法可以修改它,以获得所描述的总计?

你能否修改你的公式,以包括 @ 符号?例如 @gR21-@gR31 - Knaģis
不是很清楚。 条件是“Designation=Formule”,但结果也显示了带有“comptes”的记录。 LineTotalisationId是单列吗?那么该列下的数据看起来像是多列。 - Lali
我认为这种方法可能过于复杂了,所以要退一步,将问题分解成几个部分。我的做法是创建一个用户定义的函数来计算每个记录的“值”,然后可能将其用作计算列。查看循环以解释公式的各个部分,然后获取所需的值。 - Andy
为什么会更改 PERM?它被描述为 COMPTES 而不是 FORMULE - Gordon Linoff
3个回答

4
如果你的样本数据代表了你的数据,那么你可以尝试下面的SQL语句。 假设Panel是你的表名,而Line字段只有一个字符串字符。
Declare @sql nvarchar(max);


;
set @sql = 
'with cte as
(
 select 
    case
        when patindex(''%[A-Z]%'',Line) > 0
        then
            substring(line,patindex(''%[A-Z]%'',Line),1)
    end as LineId
    ,Totalisation
 from panel
 where line in (' +''''+ (select replace(a.totalisation,'+',''',''') from Panel a where Designation='formule' ) + '''' +')
 union all
 select 
    Line as LineId,
    Totalisation
 from panel 
 where line not in (' +''''+ (select replace(a.totalisation,'+',''',''') from Panel a where Designation='formule' ) + '''' +') 
 and Designation <> ''Formule''
'
+ ')
select 
    distinct 
    p.pnlId, p.Line, p.TotalisationId--, p.Designation ,P.Totalisation
    ,LineId, LTRIM(substring(stuff
        (
            (
             select '' | '' + c2.Totalisation from cte c2 where c.LineId = c2.LineId for xml path('''')
            )
            ,1,0,''''
        ),3,len(stuff
        (
            (
             select '' | '' + c2.Totalisation from cte c2 where c.LineId = c2.LineId for xml path('''')
            )
            ,1,0,''''
        )))
    ) as Totalisation
from cte c 
right join panel p on c.LineId = p.Line
where c.Totalisation is not null
'
;

exec(@sql)

/*
RESULT
pnlId       Line  TotalisationId LineId Totalisation
----------- ----- -------------- ------ --------------------------------------
1           A     Gross Fees     A      99999 | 99998 | 99991 | 99996 | 999995
14          B1    Perm           B1     12+14+25

*/

更新
使用@roopesh的示例数据B1 'Formula'

declare @formula nvarchar(max);
;
with cte as
(
select
    distinct 1 as id, p.Totalisation

from 
 panel2 p
 where Designation = 'formule'
) 
select
    distinct @formula =  '''' + Replace(replace(substring(stuff
            (
                (
                 select ',' + c2.Totalisation from cte c2 where c.id = c2.id for xml path('')
                )
                ,1,0,''
         ),2,len(stuff
            (
                (
                 select ',' + c2.Totalisation from cte c2 where c.id = c2.id for xml path('')
                )
                ,1,0,''
         ))),',',''','''),'+',''',''') + ''''
from cte c 

;
Declare @sql nvarchar(max);
;
set @sql = 
'
;with cte as
(
 select 
    case
        when patindex(''%[A-Z]%'',Line) > 0
        then
            substring(line,patindex(''%[A-Z]%'',Line),1)
    end as LineId
    ,Totalisation
 from panel2
 where line in (' + @formula +')
 union all
 select 
    Line as LineId,
    Totalisation
 from panel2 
 where line not in (' + @formula +') 
 and Designation <> ''Formule''
'
+ ')
select 
    distinct 
    p.pnlId, p.Line, p.TotalisationId--, p.Designation ,    p.totalisation
    ,LineId, Case when c.totalisation is null and p.designation=''Formule'' then p.totalisation
    else
        LTRIM(substring(stuff
        (
            (
             select '' | '' + c2.Totalisation from cte c2 where c.LineId = c2.LineId for xml path('''')
            )
            ,1,0,''''
        ),3,len(stuff
        (
            (
             select '' | '' + c2.Totalisation from cte c2 where c.LineId = c2.LineId for xml path('''')
            )
            ,1,0,''''
        )))
    )

    end as Totalisation
from cte c 
right join panel2 p on c.LineId = p.Line
where p.Designation = ''Formule''


'
;

exec(@sql)

@user138957,当我尝试执行这个脚本时,它给了我一个错误。我在下面的答案中尝试了我的脚本。你能确认一下我是否生成了不正确的数据吗? - Roopesh
1
当我构建查询时,示例数据使用的是B1“Comptes”,而不是B1“Formule”。如果您想使用B1“Formule”,则需要先填充总计,以便返回“A01”,“A02”,“A03”,“A04”,“A05”,“C01”,“C02”,并修改联合查询。 - cyan
先生,不用谢,祝你好运。附言:如果这个答案对您有帮助,请投票支持 :) - cyan
我该如何将这个存储过程作为递归存储过程使用?我更新了我的问题。 - user138957
@user138957 兄弟,你需要整理一下数据!你怎么会得到那种数据?当父级为A01且子级为B01+B02时,你不能使用这个查询。该查询无法连接这些数据。此外,此查询仅适用于1级深度。 - cyan

3

这个存储过程是针对你的问题提出的解决方案。它使用了游标。也许有一种方法可以去除游标,但目前还没有找到。因此,采用了这个解决方案。

CREATE Procedure [dbo].[spGetResult]
As
Begin
declare @curPNL cursor

declare @pnlid int
declare @Line varchar(10), @TotalisationId varchar(20), @Totalisation varchar(50)
declare @spresult table(PNLId int, Line varchar(10), TotalisationId varchar(20), result varchar(4000));

--declare the cursor
set @curPNL = cursor 
       for select PnlId, Line, TotalisationId, totalisation 
                 from PNLTable where designation = 'Formule'

open @curPNL

Fetch Next From @curPNL into @pnlId, @Line, @TotalisationId, @Totalisation

While @@FETCH_STATUS = 0
Begin

    declare @nsql nvarchar(4000);

    declare @table table(tname varchar(50));
    declare @result varchar(4000)

    delete from @table
    --get the totalisation data for specific column
    set @nsql = 'select totalisation from PNLTable Where Line in (''' + replace(@Totalisation,'+',''',''') + ''')';
    print 'Calling child'

    insert into @table
    exec(@nsql);

    set @result = '';

    if not exists (select 1 from @table)
        Begin
        set @result = replace(@Totalisation,'+','|') 
        End
    else
        Begin
        --get the values of totalisation in a pipe separated string
        select @result = case when @result = '' then '' else @result + '|' end + tname from @table;
        End

    --insert the values in the temporary table
    insert into @spresult(PNLId, Line, TotalisationId, result)
    select @pnlid, @Line, @TotalisationId, @result

    Fetch Next From @curPNL into @pnlId, @Line, @TotalisationId, @Totalisation
End

close @curPNL
deallocate @curPNL

select * from @spresult;
End
尽管表格结构对我来说不是很清晰,但我使用以下脚本创建了表格并插入数据。
CREATE TABLE [dbo].[PNLTable](
    [PnlId] [int] NOT NULL,
    [Line] [varchar](10) NULL,
    [TotalisationId] [varchar](20) NULL,
    [Designation] [varchar](20) NULL,
    [Totalisation] [varchar](50) NULL,
PRIMARY KEY CLUSTERED 
(
    [PnlId] ASC
)
)

--插入数据

INSERT [PNLTable] 
    ([PnlId], [Line], [TotalisationId], [Designation], [Totalisation]) 
    VALUES (1, N'A', N'Gross Fees', N'Formule', N'A01+A02+A03+A04+A05'), (2, N'A01', N'GF1', N'Comptes', N'99999')
   ,(3, N'A02', N'GF2', N'Comptes', N'99998'), (4, N'A03', N'GF3', N'Comptes', N'99991'), (5, N'A04', N'GF4', N'Comptes', N'99996')
   , (6, N'A05', N'GF5', N'Comptes', N'999995'), (14, N'B1', N'Perm', N'Formule', N'12+14+25')

我该如何将这个存储过程作为递归存储过程使用?我更新了我的问题。 - user138957

0

你需要使用公式 Designation=Formule 生成动态 SQL 查询,而不是使用一个大的静态查询。然后通过使用 EXEC 运行代码。

查询可能类似于以下内容:

SELECT
    (SELECT Totalisation WHERE Line = 'A01')
    +
    (SELECT Totalisation WHERE Line = 'A02')
    +
    (SELECT Totalisation WHERE Line = 'A03')
    +
    (SELECT Totalisation WHERE Line = 'A04')
    +
    (SELECT Totalisation WHERE Line = 'A05')
FROM [dbo].[pnlreference] AS Ref
WHERE Designation = 'Formule'
AND LEFT(Line, 1) = 'A'
也许将公式内容拆分为单个条目和运算符,并将其加载到临时表中,该表包含公式(例如A01和A02等)和ID。 然后生成代码:代码可能类似于以下内容:
EXEC 'SELECT
    (SELECT Totalisation WHERE Line = ' + CAST((SELECT Formular FROM #formular WHERE id = 0) AS VARCHAR(MAX)) +')
    ' + CAST((SELECT Operator FROM #formular WHERE id = 1) AS VARCHAR(MAX)) +'
    (SELECT Totalisation WHERE Line = ' + CAST((SELECT Formular FROM #formular WHERE id = 1) AS VARCHAR(MAX)) +')
    ' + CAST((SELECT Operator FROM #formular WHERE id = 2) AS VARCHAR(MAX)) +'
    (SELECT Totalisation WHERE Line = ' + CAST((SELECT Formular FROM #formular WHERE id = 2) AS VARCHAR(MAX)) +')
    ' + CAST((SELECT Operator FROM #formular WHERE id = 3) AS VARCHAR(MAX)) +'
    (SELECT Totalisation WHERE Line = ' + CAST((SELECT Formular FROM #formular WHERE id = 3) AS VARCHAR(MAX)) +')
    ' + CAST((SELECT Operator FROM #formular WHERE id = 4) AS VARCHAR(MAX)) +'
    (SELECT Totalisation WHERE Line = ' + CAST((SELECT Formular FROM #formular WHERE id = 4) AS VARCHAR(MAX)) +')
FROM [dbo].[pnlreference] AS Ref
WHERE Designation = ''Formule''
AND LEFT(Line, 1) = ' + LEFT(CAST((SELECT Formular FROM #formular WHERE id = 0) AS VARCHAR(MAX)), 1);

并不总是这样,A、A1、A2等仅仅是一个例子,它也可能是其他东西。 - user138957
我理解你的解释。像A1,A2等值应该从你的公式中填入动态SQL中。它也可以是B1,B2或D4F9。唯一的问题是你必须拆分公式并使用单个值和运算符生成动态SQL。使用这种方法,您可以扩展此方法以创建更复杂的公式,例如(D4G9)/A2。 - vandango

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文,