时间戳

首页 > 解决方案 > SQL 中各行的总和

sql - SQL 中各行的总和

问题描述

在 SQL 中，我们用于SUM()查找列中的总值。我想得到 100 行的总和。

如果有 5 行，单独提及列名是可以的，但写 100 个列名是困难的。是否有查找单个行总和的快捷方式？

在这个例如。如果我必须计算 1990 年的收入，我只需要计算三行会怎样？

select  
    t8.Team, 
    t7.[Revenue ($M)] as 'revenue2017',
    t8.[Revenue ($M)] as 'revenue2018',
    t9.[Revenue ($M)]  as 'revenue2019'
from
    table2018 t8
Join  
    WealthyFootballClubs.dbo.table2017 t7 On t7.Team = t8.Team 
Join  
    WealthyFootballClubs.dbo.table2019 t9 On t9.team = t8.team

输出

Team              revenue2017   revenue2018     revenue2019 
-----------------------------------------------------------
Manchester United     850          737              795         
Real Madrid           688          735              896            
Barcelona             690          706              824            
Bayern Munich         657          640              751 
Manchester City       650          575              678 
Liverpool             523          462              613

标签： sqltsql

解决方案

您知道语句的外观，因此只需使用动态 T-SQL 语句和系统视图来构建它。像这样的东西：

DECLARE @DataSource TABLE
(
    [name] SYSNAME
   ,[table_alias] VARCHAR(12)
   ,[column_alias] VARCHAR(12)
);

DECLARE @DynamicTSQLStatement NVARCHAR(MAX);

DECLARE @BaseTableName SYSNAME
       ,@BaseTableAlias VARCHAR(12);

INSERT INTO @DataSource ([name], [table_alias], [column_alias])
SELECT [name]
      ,'T' + CAST(ROW_NUMBER() OVER (ORDER BY [name] ASC) AS VARCHAR(12))
      ,'revenue' + CAST(TRY_CAST(REPLACE([name], 'table', '') AS INT) AS VARCHAR(12))
FROM [sys].[tables]
WHERE [schema_id] = SCHEMA_ID('dbo')
    AND TRY_CAST(REPLACE([name], 'table', '') AS INT) BETWEEN 1990 AND 2019;

SELECT @BaseTableName = [name]
      ,@BaseTableAlias = [table_alias]
FROM @DataSource
WHERE [name] <> 'table2018';







SET @DynamicTSQLStatement = 'SELECT ' + @BaseTableAlias + '.[Team]';

SELECT @DynamicTSQLStatement = @DynamicTSQLStatement + STUFF
(
    (
        SELECT ',' + [table_alias] + '.[Revenue ($M)] AS [' + [column_alias] + ']'
        FROM @DataSource 
        ORDER BY [name]
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
   ,1
   ,0
   ,''
);

SET @DynamicTSQLStatement = @DynamicTSQLStatement + ' FROM ' + @BaseTableName + ' ' + @BaseTableAlias + ' ';

SELECT @DynamicTSQLStatement = @DynamicTSQLStatement + STUFF
(
    (
        SELECT ' INNER JOIN [dbo].[' + [name] + '] AS ' + [table_alias] + ' ON ' + [table_alias] + '.[team] = ' + @BaseTableAlias + '.[Team]' 
        FROM @DataSource
        WHERE [name] <> @BaseTableName
        ORDER BY [name]
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
   ,1
   ,0
   ,''
);

EXEC sp_executesql @DynamicTSQLStatement;

我TRY_CAST只用来获取我感兴趣的表格，但如果您的版本不支持此功能，您可以更改过滤条件。其余代码应适用于每个版本。

推荐阅读