PostgreSQL分组查询：如何在获取名称计数时同时返回ID

当然有办法实现啦！从你的示例来看用的是PostgreSQL，这里给你两种实用的方案，看你具体需求来选：

情况1：每个ID行都显示对应name的总计数 #

如果你想保留每一行的ID，同时在每行后面标注该name的总行数，用窗口函数是最优解，性能也更好：

SELECT id, name, COUNT(*) OVER (PARTITION BY name) AS name_count
FROM hello
WHERE name IN ('abc', 'dfg');

执行后结果会是这样：

id | name | name_count
----+------+------------
  1 | abc  |          3
  3 | abc  |          3
  5 | abc  |          3
  4 | dfg  |          1

窗口函数COUNT(*) OVER (PARTITION BY name)会为每个相同name的行计算出该name的总行数，全程只需要扫描一次表，效率拉满。

情况2：分组后同时显示该组的所有ID和计数 #

如果你想保持原来的分组聚合结构，同时把该组的所有ID列出来，可以用聚合函数STRING_AGG（把ID拼成字符串）或者ARRAY_AGG（把ID存成数组）：

用字符串拼接ID的示例

SELECT 
  name, 
  COUNT(*) AS name_count, 
  STRING_AGG(id::TEXT, ', ') AS associated_ids
FROM hello
WHERE name IN ('abc', 'dfg')
GROUP BY name;

结果如下：

name | name_count | associated_ids
------+------------+----------------
 dfg  |          1 | 4
 abc  |          3 | 1, 3, 5

用数组存储ID的示例

SELECT 
  name, 
  COUNT(*) AS name_count, 
  ARRAY_AGG(id) AS associated_ids
FROM hello
WHERE name IN ('abc', 'dfg')
GROUP BY name;

结果会是：

name | name_count | associated_ids
------+------------+----------------
 dfg  |          1 | {4}
 abc  |          3 | {1,3,5}

补充：关联子查询的方式（不推荐大数据量） #

还有一种用子查询的实现方式，虽然能达到效果，但性能不如窗口函数，只适合小数据量场景：

SELECT 
  h.id, 
  h.name, 
  (SELECT COUNT(*) FROM hello WHERE name = h.name) AS name_count
FROM hello h
WHERE h.name IN ('abc', 'dfg');

结果和情况1的窗口函数结果一致，但大数据量下可能会多次扫描表，所以优先选窗口函数。

内容的提问来源于stack exchange，提问作者wonder