主页 / 问题 / 1040172
Accepted
Rekssel
Asked:2020-10-30 08:41:22 +0000 UTC2020-10-30 08:41:22 +0000 UTC

将汇总行添加到 TSQL 查询结果

  • 772

有一个动态编译的查询，即 其中的字段数量各不相同。每个动态字段代表float一个值。查询本身会在每个字段中生成运行总计结果。最后一行应该是最后一行，包含每个字段的最后一行和第一行之间的差异。第一个字段是固定的，具有“标签功能”，即简单地表示日期或总计。

这是字段定义的样子：

    SELECT
        @CellsFields = @CellsFields + ',' + 
        'ROUND(MAX(CASE WHEN val.ObjectLocationId = '+CONVERT(varchar(10), loc.Id)+' and val.ParametrId = 20 THEN val.VL ELSE 0 END),3) as [' + loc.ShortName+', значение1] '+','+
        'ROUND(MAX(CASE WHEN val.ObjectLocationId = '+CONVERT(varchar(10), loc.Id)+' and val.ParametrId = 22 THEN val.VL ELSE 0 END),3) as [' + loc.ShortName+', значение2] '        
    FROM
        [dbo].[PokazateliBlaBla] loc
    WHERE   
        loc.ObjectId = @ObjectId

这就是请求本身的调用方式：

SET @SQL =
    '

    SELECT
        CONVERT(varchar(10),val.TM,104) as ''Дата'''+@CellsFields+'
    FROM
        [dbo].[ValuesBlaBla] val
    WHERE
        val.ParametrID in (20,22) and
        val.TM between '''+@DateStart+' 00:00:00'' and '''+@DateEnd+' 23:59:59''
    GROUP BY
        CONVERT(varchar(10),val.TM,104)
    ORDER BY
        CONVERT(varchar(10),val.TM,104)

    '

    EXEC(@SQL)

结果将是这样的集合： 在此处输入图像描述

除了最后一行，我想通了。我不知道如何获得第一个值和最后一个值之间的差异。如果您依赖图片，则需要从 中减去 的01.09.19值30.09.19。我会很高兴任何理事会和有关可能错误的决定方法的迹象。

sql

1 个回答

  1. Best Answer

    弄成这样。首先，我生成了与最终字段配对的常规字段：

    SELECT
        @CellsFields = @CellsFields + ',' + 
        'ROUND(MAX(CASE WHEN val.ObjectLocationId = '+CONVERT(varchar(10), loc.Id)+' and val.ParametrId = 20 THEN val.VL ELSE 0 END),3) as [' + loc.ShortName+', значение1] '+','+
        'ROUND(MAX(CASE WHEN val.ObjectLocationId = '+CONVERT(varchar(10), loc.Id)+' and val.ParametrId = 22 THEN val.VL ELSE 0 END),3) as [' + loc.ShortName+', значение2] ',
        @TotalFields = @TotalFields + ',' +
        'ROUND(MAX(CASE WHEN val.ObjectLocationId = '+CONVERT(varchar(10), loc.Id)+' and val.ParametrId = 20 and (val.TM between '''+@DateEnd+' 00:00:00'' and '''+@DateEnd+' 23:59:59'') THEN val.VL ELSE 0 END)-'+
        'MAX(CASE WHEN val.ObjectLocationId = '+CONVERT(varchar(10), loc.Id)+' and val.ParametrId = 20 and (val.TM between '''+@DateStart+' 00:00:00'' and '''+@DateStart+' 23:59:59'') THEN val.VL ELSE 0 END),3) as ''' + loc.ShortName+', значение1'' '+','+
        'ROUND(MAX(CASE WHEN val.ObjectLocationId = '+CONVERT(varchar(10), loc.Id)+' and val.ParametrId = 22 and (val.TM between '''+@DateEnd+' 00:00:00'' and '''+@DateEnd+' 23:59:59'') THEN val.VL ELSE 0 END)-'+
        'MAX(CASE WHEN val.ObjectLocationId = '+CONVERT(varchar(10), loc.Id)+' and val.ParametrId = 22 and (val.TM between '''+@DateStart+' 00:00:00'' and '''+@DateStart+' 23:59:59'') THEN val.VL ELSE 0 END),3) as ''' + loc.ShortName+', значение2'' '       
    FROM
        [Krug].[dbo].[ObjectLocation] loc
    WHERE   
        loc.ObjectId = @ObjectId

    然后已经组合UNION ALL并排序：

    SET @SQL =
    '
    SELECT * FROM (
    SELECT
        CONVERT(varchar(10),val.TM,104) as ''Дата'''+@CellsFields+'
    FROM
        [dbo].[ValuesBlaBla] val
    WHERE
        val.ParametrID in (20,22) and
        val.TM between '''+@DateStart+' 00:00:00'' and '''+@DateEnd+' 23:59:59''
    GROUP BY
        CONVERT(varchar(10),val.TM,104)
    UNION ALL    
    SELECT
        ''ИТОГО'' as ''Дата'''+@TotalFields+'
    FROM
        [dbo].[ValuesBlaBla] val
    WHERE
        val.ParametrID in (20,22) and
        val.TM between '''+@DateStart+' 00:00:00'' and '''+@DateEnd+' 23:59:59'') t
    ORDER BY [Дата]
    '

    EXEC(@SQL)
    • 0

相关问题