我们可以用 UNION ALL 将名称与其对应的得分值合并到一列中。现在，我们可以使用这个结果集作为派生表 GROUP BY 上 name 去拿那个 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

结果

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

db fiddle视图

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