使用 GROUP BY 和 JOIN 进行 MySQL 查询

Question

下午好

我正在尝试从我的 MySQL 数据库中获取一些信息，但遇到了问题，因为我无法获得所需的信息。我尝试了很多不同的方法，但都没有奏效。我希望你能找到一些东西，因为我非常接近找到解决方案但缺少一些东西:

MySQL 查询:

SELECT b.id, b.tipo_perfil, round(avg(b.edad)), COUNT(c.zona), c.zona 
FROM analizador_datos_usuario AS a 
INNER JOIN analizador_datos_perfil AS b ON (a.id_usuario = b.id_perfil)
INNER JOIN analizador_datos_perfil_historial AS c ON (b.id = c.id_perfil)
WHERE a.id_usuario=21 
GROUP BY b.tipo_perfil, c.zona  
ORDER BY b.tipo_perfil ASC, count(c.zona) DESC

此查询为我提供了以下信息:

表格(红色部分是我需要的):

亲切的问候，

Answer 1

试试看:

SELECT  b.tipo_perfil, round(avg(b.edad)), COUNT(distinct c.zona), group_concat(distinct b.id separator ' ') as id_list, group_concat(distinct c.zona separator ' ') as zona_list
FROM analizador_datos_usuario AS a 
INNER JOIN analizador_datos_perfil AS b ON (a.id_usuario = b.id_perfil)
INNER JOIN analizador_datos_perfil_historial AS c ON (b.id = c.id_perfil)
WHERE a.id_usuario=21 
GROUP BY b.tipo_perfil
ORDER BY b.tipo_perfil ASC, count(distinct c.zona) DESC