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

我有一张桌子

| 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语法是什么?

回答(2)

2 years ago

我们可以使用 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

2 years ago

使用 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