mysql - MYSQL：根据按其他列分组的计数设置列值

Question

我有一张桌子，它有以下几列：

+-----------------------------+-------+---------------+----------------+
| School_Name                 | Class | ClassFiveSize | ClassEightSize |
+-----------------------------+-------+---------------+----------------+
| Tando Ghulam Ali-I          |     5 |          NULL |           NULL |
| Tando Ghulam Ali-I          |     5 |          NULL |           NULL |
| Tando Ghulam Ali-I          |     8 |          NULL |           NULL |
| Model School (E.M) Larkano. |     5 |          NULL |           NULL |
| Model School (E.M) Larkano. |     5 |          NULL |           NULL |
| Model School (E.M) Larkano. |     8 |          NULL |           NULL |
| Model School (E.M) Larkano. |     5 |          NULL |           NULL |
| Model School (E.M) Larkano. |     8 |          NULL |           NULL |

我想根据学校名称的数量在 ClassFiveSize 和 ClassEightSize 列中设置值，例如：

+-----------------------------+-------+---------------+----------------+
| School_Name                 | Class | ClassFiveSize | ClassEightSize |
+-----------------------------+-------+---------------+----------------+
| Tando Ghulam Ali-I          |     5 |          2    |           1    |
| Tando Ghulam Ali-I          |     5 |          2    |           1    |
| Tando Ghulam Ali-I          |     8 |          2    |           1    |
| Model School (E.M) Larkano. |     5 |          3    |           2    |
| Model School (E.M) Larkano. |     5 |          3    |           2    |
| Model School (E.M) Larkano. |     8 |          3    |           2    |
| Model School (E.M) Larkano. |     5 |          3    |           2    |
| Model School (E.M) Larkano. |     8 |          3    |           2    |

请让我知道该怎么做。

我正在做这样的事情：

update tableName t
inner join (
    select School_Name, count(*) counter
    from tableName
    where Class=8  group by School_Name ) g on g.School_Name = t.School_Name
set t.ClassEightSize = case when t.Class=8 then g.counter else 0 end;

标签： mysql

Answer 1

您可以在查询中使用条件聚合，该查询返回每种情况的计数器并将其连接到表中：

update tablename t inner join ( 
  select School_Name, 
    sum(Class = 5) counter5,
    sum(Class = 8) counter8
  from tablename  
  group by School_Name 
) g on g.School_Name = t.School_Name 
set
  t.ClassFiveSize = g.counter5,
  t.ClassEightSize = g.counter8;

请参阅演示。
结果：

| School_Name                 | Class | ClassFiveSize | ClassEightSize |
| --------------------------- | ----- | ------------- | -------------- |
| Tando Ghulam Ali-I          | 5     | 2             | 1              |
| Tando Ghulam Ali-I          | 5     | 2             | 1              |
| Tando Ghulam Ali-I          | 8     | 2             | 1              |
| Model School (E.M) Larkano. | 5     | 3             | 2              |
| Model School (E.M) Larkano. | 5     | 3             | 2              |
| Model School (E.M) Larkano. | 8     | 3             | 2              |
| Model School (E.M) Larkano. | 5     | 3             | 2              |
| Model School (E.M) Larkano. | 8     | 3             | 2              |