Mysql计数列值和合并列

Question

我在根据给定的字段值分组来创建计数行时遇到了问题。例如:我有一个表--结构，如下所示：

+------+------------+
| 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;

Answer 2

它是一个简单的left join，有一个group by

select tableA.person, sum(tableB.points) from tableA left join tableB on tableA.person = tableB.person group by tableA.person
union
select tableB.person, sum(tableB.points) from tableB left join tableA on tableA.person = tableB.person  where tableA.id is null group by tableA.person

Answer 3

我认为下面的sql对您有用。

select a.id, a.Person,b.total_point  from (
select id, Person from tablea) as a join

(select Person, sum(Point) as total_point from tableb group by person) as b on a.person =b.person

谢谢