如何从SQL中的多列中获取值之和

Question

我有下表：

Season  Name1   Goal1   Name2   Goal2   Name3   Goal3
1990    Smith   2       Abel    1       John    3
1990    Smith   1       Abel    1       John    1
1990    John    0       Smith   2       Abel    5
1991    John    1       Smith   1       Abel    2
1991    Abel    2       John    0       Smith   0
1992    Abel    3       John    0       Smith   1

赛季表示足球赛季，name1，name2表示球员在给定比赛中的位置Goal1表示Name1进球数

我想为每个赛季的每个名字生成一个列表，列出他们踢了多少次球，以及他们进球的数量。类似这样：

Abel 1990 3 games played 7 goals scored
Abel 1991 2 games played 4 goals scored 
Abel 1992 1 games played 3 goals scored 
John 1990 3 games played 2 goals scored

任何帮助都将不胜感激！

Answer 1

这真的很复杂，但您可以编写一个内联查询，将所有内容都转换为适当的表格式，这样您就可以进行常规的聚合和分组。根据有多少列，继续为1到x添加联合。

SELECT Season, Name, Sum(Goals)
FROM (SELECT Season, Name1 as Name, Goals1 as Goals        
        FROM table
      UNION
      SELECT Season, Name2 as Name, Goals2 as Goals        
        FROM table
      UNION
      SELECT Season, Name3 as Name, Goals3 as Goals        
        FROM table) newtable
GROUP BY Season, Name

Answer 2

好吧，既然你没有共享你正在使用的RDBMS，我认为这个（丑陋的）查询可以在大多数RDBMS上运行：

SELECT  Name + ' ' + CAST(Season AS VARCHAR(4)) + ' ' +
        CAST(Games AS VARCHAR(4)) + ' games played ' + 
        CAST(Goals AS VARCHAR(4)) + ' goals scored' AS YourColumn
FROM (  SELECT Season, Name, SUM(Goals) AS Goals, COUNT(*) AS Games
        FROM (  SELECT Season, Name1 AS Name, Goal1 AS Goals
                FROM YourTable
                UNION ALL
                SELECT Season, Name2 AS Name, Goal2 AS Goals
                FROM YourTable
                UNION ALL
                SELECT Season, Name3 AS Name, Goal3 AS Goals
                FROM YourTable) AS A
        GROUP BY Season, Name) X

免责声明：这是一个丑陋的查询。

Answer 3

SELECT
    sub.player,
    sub.Season,
    Count(*) AS games_played,
    Sum(sub.goals) AS SumOfgoals
FROM
    (
        SELECT Season, Name1 AS player, Goal1 AS goals
        FROM YourTable
        UNION ALL
        SELECT Season, Name2, Goal2
        FROM YourTable
        UNION ALL
        SELECT Season, Name3, Goal3
        FROM YourTable
    ) AS sub
GROUP BY sub.player, sub.Season
ORDER BY sub.player, sub.Season;

注意，您必须在该子查询中使用UNION ALL。如果您只使用UNION，子查询结果集将只包括赛季、球员和进球的每个组合的一行。但是，当一个球员在一个赛季的多场比赛中得分相同数量的进球时，您希望保留其中的每一行，以便准确计算所进行的比赛和总进球数。

使用Access 2007中的样本数据，该查询生成此结果集。

player Season games_played SumOfgoals
Abel     1990            3          7
Abel     1991            2          4
Abel     1992            1          3
John     1990            3          4
John     1991            2          1
John     1992            1          0
Smith    1990            3          5
Smith    1991            2          1
Smith    1992            1          1