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

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

  回复于 2024-04-27T07:06:22+08:00
• 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

  回复于 2024-04-27T07:06:22+08:00