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

Question

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

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

有没有一种方法可以修改它，以获得所描述的总计？

- user138957

你能否修改你的公式，以包括 @ 符号？例如 @gR21-@gR31？ - Knaģis

不是很清楚。条件是“Designation=Formule”，但结果也显示了带有“comptes”的记录。 LineTotalisationId是单列吗？那么该列下的数据看起来像是多列。 - Lali

我认为这种方法可能过于复杂了，所以要退一步，将问题分解成几个部分。我的做法是创建一个用户定义的函数来计算每个记录的“值”，然后可能将其用作计算列。查看循环以解释公式的各个部分，然后获取所需的值。 - Andy

为什么会更改 PERM？它被描述为 COMPTES 而不是 FORMULE。 - Gordon Linoff

3个回答

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')

- Roopesh

我该如何将这个存储过程作为递归存储过程使用？我更新了我的问题。 - 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);

- vandango

并不总是这样，A、A1、A2等仅仅是一个例子，它也可能是其他东西。 - user138957

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

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，

- cyan · Accepted Answer

如果你的样本数据代表了你的数据，那么你可以尝试下面的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)