如何在SQL中根据条件动态对列执行乘法运算

现有一段SQL Server动态查询语句，需要添加条件逻辑：当变量@Multiplier的值为1时，将指定的财务比率列乘以100，且乘完后列的别名必须与原列名保持一致。

原动态SQL代码 #

DECLARE @companyids nvarchar(1024) = '77',
@fromYear Int = 2015,
@toYear Int = 2023,
@fiscalPeriodType Int = 3,
@lang smallint = 1,
@frIds nvarchar(1024) = '109,4'

Declare @sql As nvarchar(max), @periods nvarchar(max), @financialRatioTable nvarchar(max), @financialRatioNames nvarchar(max) , @Id  nvarchar(max) , @Multiplier bit
    Select @sql = N'', @periods = N'', @financialRatioTable = N'', @financialRatioNames = N'' , @Id = N'' , @Multiplier = 0

Select @financialRatioNames = STRING_AGG(ColumnName, ',') FROM FinancialRatioConfigs WHERE FinancialRatioID IN (SELECT value FROM STRING_SPLIT(@frIds, ','))

SELECT  @Id =  (SELECT CAST(LEFT(@frIds, CHARINDEX(',', @frIds + ',') - 1) AS INT)) 

Select @Multiplier =  ShowPercentage FROM FinancialRatioConfigs WHERE FinancialRatioID = @Id

Select distinct @financialRatioTable = F.AssociatedFRTable
From CompanyArgaamSectors CAS
Inner Join ArgaamSectorFSTemplates AFS On AFS.ArgaamSectorID = CAS.ArgaamSectorID
Inner Join FSTemplates F On AFS.FSTemplateID = F.FSTemplateID
Where CAS.CompanyID in (SELECT value FROM STRING_SPLIT(@companyids, ','))
and AFS.IsActive = 1
and CAS.IsDefault = 1

Select @sql = N'
Select * from (
            Select CompanyID, CompanyNameAr, CompanyNameEn, FinancialYear, FiscalPeriodID, FiscalPeriodTypeID, FiscalPeriodValue, ForDate,
            '+@financialRatioNames+'
            From '+@financialRatioTable+'
            Where CompanyID In('+@companyids +') And FiscalPeriodTypeID = '+Cast(@fiscalPeriodType as nvarchar) +' And IsPublish = 1
            AND FinancialYear >= '+CAST(@fromYear AS nvarchar(10))+' AND FinancialYear <= '+CAST(@toYear AS nvarchar(10)) +'
            )V
            Order By ForDate Asc'

  Select * from (
                Select CompanyID, CompanyNameAr, CompanyNameEn, FinancialYear, FiscalPeriodID, FiscalPeriodTypeID, FiscalPeriodValue, ForDate,
                BookValue,TotalShareholdersEquity
                From FROthers
                Where CompanyID In(77) And FiscalPeriodTypeID = 3 And IsPublish = 1
                AND FinancialYear >= 2015 AND FinancialYear <= 2023
                )V
                Order By ForDate Asc

修改后的动态SQL代码 #

DECLARE @companyids nvarchar(1024) = '77',
@fromYear Int = 2015,
@toYear Int = 2023,
@fiscalPeriodType Int = 3,
@lang smallint = 1,
@frIds nvarchar(1024) = '109,4'

Declare @sql As nvarchar(max), @periods nvarchar(max), @financialRatioTable nvarchar(max), @financialRatioNames nvarchar(max) , @Id  nvarchar(max) , @Multiplier bit
    Select @sql = N'', @periods = N'', @financialRatioTable = N'', @financialRatioNames = N'' , @Id = N'' , @Multiplier = 0

SELECT  @Id =  (SELECT CAST(LEFT(@frIds, CHARINDEX(',', @frIds + ',') - 1) AS INT)) 

Select @Multiplier =  ShowPercentage FROM FinancialRatioConfigs WHERE FinancialRatioID = @Id

-- 根据@Multiplier的值生成带条件乘100的列表达式
SELECT @financialRatioNames = STRING_AGG(
    CASE WHEN @Multiplier = 1 THEN CONCAT(ColumnName, ' * 100 AS ', ColumnName) ELSE ColumnName END,
    ','
) FROM FinancialRatioConfigs WHERE FinancialRatioID IN (SELECT value FROM STRING_SPLIT(@frIds, ','))

Select distinct @financialRatioTable = F.AssociatedFRTable
From CompanyArgaamSectors CAS
Inner Join ArgaamSectorFSTemplates AFS On AFS.ArgaamSectorID = CAS.ArgaamSectorID
Inner Join FSTemplates F On AFS.FSTemplateID = F.FSTemplateID
Where CAS.CompanyID in (SELECT value FROM STRING_SPLIT(@companyids, ','))
and AFS.IsActive = 1
and CAS.IsDefault = 1

Select @sql = N'
Select * from (
            Select CompanyID, CompanyNameAr, CompanyNameEn, FinancialYear, FiscalPeriodID, FiscalPeriodTypeID, FiscalPeriodValue, ForDate,
            '+@financialRatioNames+'
            From '+@financialRatioTable+'
            Where CompanyID In('+@companyids +') And FiscalPeriodTypeID = '+Cast(@fiscalPeriodType as nvarchar) +' And IsPublish = 1
            AND FinancialYear >= '+CAST(@fromYear AS nvarchar(10))+' AND FinancialYear <= '+CAST(@toYear AS nvarchar(10)) +'
            )V
            Order By ForDate Asc'

-- 对FROthers的查询也应用同样的条件逻辑
DECLARE @frothersColumns nvarchar(max)
SELECT @frothersColumns = 
    CASE WHEN @Multiplier = 1 THEN 'BookValue * 100 AS BookValue,TotalShareholdersEquity * 100 AS TotalShareholdersEquity'
         ELSE 'BookValue,TotalShareholdersEquity' END

EXEC(N'
Select * from (
                Select CompanyID, CompanyNameAr, CompanyNameEn, FinancialYear, FiscalPeriodID, FiscalPeriodTypeID, FiscalPeriodValue, ForDate,
                '+@frothersColumns+'
                From FROthers
                Where CompanyID In('+@companyids +') And FiscalPeriodTypeID = '+Cast(@fiscalPeriodType as nvarchar) +' And IsPublish = 1
                AND FinancialYear >= '+CAST(@fromYear AS nvarchar(10))+' AND FinancialYear <= '+CAST(@toYear AS nvarchar(10)) +'
                )V
                Order By ForDate Asc')

关键修改说明 #

1. 调整执行顺序：先获取@Multiplier的值，再生成带条件的列表达式，避免因变量未赋值导致逻辑失效。
2. 动态生成列表达式：使用STRING_AGG结合CASE语句，当@Multiplier=1时自动生成列名 * 100 AS 列名的表达式，否则直接使用原列名。
3. 统一处理FROthers查询：将硬编码的列和参数改为动态拼接，保持与主查询的逻辑一致性，同时支持变量参数的复用。
4. 别名一致性：确保乘100后的列别名与原列名完全相同，避免结果集结构发生变化。

内容的提问来源于stack exchange，提问作者Faizan Naeem