php - 连接 MySql 结果

Question

我有这个查询将搜索一些新闻并将新闻数据作为其组、公司和类别返回。问题是一个新闻可以有很多公司和团体，我需要将这个结果(grupoNome 和 empresaNome)分别连接到一个字段中，因此使这个查询只返回一行:

这是我的“正常”查询结果:

id  titulo  categoriaId url     categoriaNome       grupoNome   empresaNome
5   Teste   1           teste   "Todas as Notícas"  "Group 1"   Company 1
5   Teste   1           teste   "Todas as Notícas"  "Group 1"   Company 2
5   Teste   1           teste   "Todas as Notícas"  "Group 1"   Company 3
5   Teste   1           teste   "Todas as Notícas"  "Group 1"   Company 4
5   Teste   1           teste   "Todas as Notícas"  "Group 2"   Company 1
5   Teste   1           teste   "Todas as Notícas"  "Group 2"   Company 2
5   Teste   1           teste   "Todas as Notícas"  "Group 2"   Company 3
5   Teste   1           teste   "Todas as Notícas"  "Group 2"   Company 4
5   Teste   1           teste   "Todas as Notícas"  "Group 3"   Company 1
5   Teste   1           teste   "Todas as Notícas"  "Group 3"   Company 2
5   Teste   1           teste   "Todas as Notícas"  "Group 3"   Company 3
5   Teste   1           teste   "Todas as Notícas"  "Group 3"   Company 4

这是我的查询:

SELECT n.*, nc.nome AS categoriaNome, g.nome AS grupoNome, e.nome AS empresaNome FROM `noticias` n
INNER JOIN `noticiascategorias` nc ON n.categoriaId = nc.id
INNER JOIN `noticiasgrupos` ng ON n.id = ng.noticiaId
INNER JOIN `grupos` g ON g.id = ng.grupoId
INNER JOIN `noticiasempresas` ne ON n.id = ne.noticiaId
INNER JOIN `empresas` e ON e.id = ne.empresaId
WHERE n.url LIKE 'teste';

noticiasgrupos/noticiasempresas 包含新闻和集团/公司的多对多关系。

grupos/empresas 包含集团/公司信息。

编辑:

我需要这样的结果:

5   Teste   1   teste   "Todas as Notícas"  "Group 1_Group2_Group_3" "Company 1_Company_Company_3"

除数可以是我以后可以用来拆分的任何东西 (| _ *)

Answer 1

您可以使用 GROUP_CONCAT 来执行此操作。只需将 GROUP BY 设置为您想要分组的任何内容。您还可以为 GROUP_CONCAT 指定排序顺序和分隔符。

SELECT n.*, nc.nome AS categoriaNome, 
       GROUP_CONCAT(DISTINCT g.nome ORDER BY g.nome ASC SEPARATOR '_' ) AS grupoNome,
       GROUP_CONCAT(DISTINCT e.nome ORDER BY g.nome ASC SEPARATOR '_' ) AS EmpresaNome
FROM `noticias` n
INNER JOIN `noticiascategorias` nc ON n.categoriaId = nc.id
INNER JOIN `noticiasgrupos` ng ON n.id = ng.noticiaId
INNER JOIN `grupos` g ON g.id = ng.grupoId
INNER JOIN `noticiasempresas` ne ON n.id = ne.noticiaId
INNER JOIN `empresas` e ON e.id = ne.empresaId
WHERE n.url LIKE 'teste'
GROUP BY n.id;