MySQL在同一个表中连接列和计数值

loading...


-1

我有一张桌子

| id | p1_name | p1_score | p2_name | p2_score |
------------------------------------------------
|  1 | aaa     | 2        | bbb     | 3        |
|  1 | ccc     | 1        | ddd     | 7        |
|  1 | ddd     | 5        | aaa     | 8        |

我需要的是计算所有玩家的所有分数,并以更简单的形式显示,如:

| name | score |
----------------
| aaa  |  10   |
| bbb  |   3   |
| ccc  |   1   |
| ddd  |  12   |

实现此目的的SQL语法是什么?

loading...

2回答

  • 0

    我们可以使用 UNION ALL 将名称与其对应的分数值组合成一列 . 现在,我们可以将此结果集用作派生表,并在 name 上使用 GROUP BY 并获得分数的 SUM() .

    SELECT 
      dt.name, 
      SUM(dt.score) AS score
    FROM 
    (
      SELECT
        p1_name AS name, 
        p1_score AS score
      FROM your_table
      UNION ALL
      SELECT
        p2_name AS name, 
        p2_score AS score
      FROM your_table
    ) AS dt
    GROUP BY dt.name
    

    Result

    | name | score |
    | ---- | ----- |
    | aaa  | 10    |
    | bbb  | 3     |
    | ccc  | 1     |
    | ddd  | 12    |
    

    View on DB Fiddle


  • 1

    使用 Union All 进行分组:

    select q.name , sum(q.score) as score
      from
        (
        select p1_name as name, p1_score as score
          from tab
        union all 
        select p2_name as name, p2_score
          from tab
        ) q
    group by q.name;
    

    Rextester Demo

评论

暂时没有评论!