Mysql统计列值和合并列

Question

我在通过基于给定字段值的分组来创建计数行时遇到了问题。例如:我有一个像这样的 Table A 结构:

+------+------------+
| id   |  Person    |
+------+------------+
| 1    | "Sandy"    |
| 2    | "Piper"    |
| 3    | "Candy"    |
| 4    | "Pendy"    |
+------------+------+

我还有一个像这样的表B结构:

+------+------------+---------+
| id   |  Person    |  Point  |
+------+------------+---------+
| 1    | "Sandy"    |  10     |
| 2    | "Piper"    |  20     |
| 3    | "Candy"    |  30     |
| 4    | "Sandy"    |  10     |
| 5    | "Piper"    |  20     |
| 6    | "Zafar"    |  30     |
+------------+------+---------+

需要这样的结果:

+------+------------+---------+
| id   |  Person    |  Point  |
+------+------------+---------+
| 1    | "Piper"    |  40     |
| 2    | "Candy"    |  30     |
| 3    | "Zafar"    |  30     |
| 4    | "Sandy"    |  20     |
| 5    | "Pendy"    |   0     |
+------------+------+---------+

我希望表格示例本身是不言自明的。

Answer 1

SELECT person
     , SUM(point) total 
  FROM 
     ( SELECT person,point FROM table_b
        UNION 
          ALL
       SELECT person,0 FROM table_a
     ) x
 GROUP 
    BY person 
 ORDER 
    BY total DESC;