MySQL 查询获取唯一值的小计

Question

我有以下数据:
+---+----+
| key |名称|
+---+----+
|1 |汤姆 |
+---+----+
|2 |汤姆 |
+---+----+
|3 |丹|
+---+----+
|4 |丹 |
+---+----+
|5 |丹 |
+---+----+
|6 |丹 |
+---+----+
|7 |肯 |
+---+----+
|8 |肯 |
+---+----+


并且需要 MySQL 查询才能得到这个
+---+----+------------+
| key |名称|小计 |
+---+----+------------+
|1 |汤姆 | 2 |
+---+----+------------+
|2 |汤姆 | 2 |
+---+----+------------+
|3 |丹| 4 |
+---+----+------------+
|4 |丹 | 4 |
+---+----+------------+
|5 |丹 | 4 |
+---+----+------------+
|6 |丹 | 4 |
+---+----+------------+
|7 |肯 | 2 |
+---+----+------------+
|8 |肯 | 2 |
+---+----+------------+
相同的数据，但附加列显示名称列中唯一名称的数量。我尝试使用 count(1) 和 group by，但结果为 3 个名称条目和总计，但我需要每条记录都包含有关有多少记录与名称列中具有相同值的附加信息

Answer 1

select t1.id, t1.name, t2.total
from your_table t1
join
(
  select name, count(*) as total
  from your_table
  group by name
) t2 on t1.name = t2.name