如何使用MySQL查询ID到访地点列表及解决子查询返回多值报错

你编写的子查询会为每个id返回多条location记录，SQL语法不允许在select子句的表达式位置返回多行结果，因此触发该报错。

核心逻辑为在group by的聚合规则内，同时完成去重地点数统计、去重地点列表拼接的需求，不需要额外编写独立的子查询，不同数据库的对应写法如下：

• SQL Server 2017及以上 / PostgreSQL

SELECT 
  prim.id, 
  COUNT(DISTINCT prim.location) AS distinct_location_count,
  STRING_AGG(DISTINCT prim.location, ', ') AS visited_location_list
FROM dbo.Events_compiled AS prim 
GROUP BY prim.id 
ORDER BY distinct_location_count DESC;

STRING_AGG的第二个参数为拼接分隔符，可根据需求替换为分号、换行符等。

• MySQL

SELECT 
  prim.id, 
  COUNT(DISTINCT prim.location) AS distinct_location_count,
  GROUP_CONCAT(DISTINCT prim.location SEPARATOR ', ') AS visited_location_list
FROM dbo.Events_compiled AS prim 
GROUP BY prim.id 
ORDER BY distinct_location_count DESC;

如果拼接的地点列表过长超出默认长度限制，可提前调整group_concat_max_len参数。

• Oracle 11gR2及以上

SELECT 
  prim.id, 
  COUNT(DISTINCT prim.location) AS distinct_location_count,
  LISTAGG(DISTINCT prim.location, ', ') WITHIN GROUP (ORDER BY prim.location) AS visited_location_list
FROM Events_compiled prim 
GROUP BY prim.id 
ORDER BY distinct_location_count DESC;

• SQL Server 2016及以下（无STRING_AGG函数）
  可通过FOR XML PATH语法实现拼接：

SELECT 
  prim.id,
  COUNT(DISTINCT prim.location) AS distinct_location_count,
  STUFF((
    SELECT DISTINCT ', ' + location 
    FROM dbo.Events_compiled sub
    WHERE sub.id = prim.id
    FOR XML PATH(''), TYPE
  ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS visited_location_list
FROM dbo.Events_compiled AS prim
GROUP BY prim.id
ORDER BY distinct_location_count DESC;

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