我有一个查询,它将搜索一些新闻,并将新闻数据作为其组、公司和类别返回。问题是,一条新闻可能有许多公司和团体,我需要将这些结果(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"
除数可以是我以后可以用来分割的任何东西(|_*)
您可以使用GROUP_CONCT来执行此操作。只需将你的GROUP BY
设置为你想分组的任何值。你也可以为group_CONCT指定排序顺序和分隔符。
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;