JOIN查询MySQL中GROUP_CONCAT的问题 [英] issue with GROUP_CONCAT in JOIN query mysql

问题描述

我有一个联接查询，在其中我左联接了2个表，分别是tab_sector和tab_sector_subdivisions.我在第一个表中有记录，第二个表中可能有也可能没有对应的记录.我以这种方式连接这些表，它应该返回第一个表的所有行和第二个表的匹配行.同样，如果第一个表中的行在第二个表中有多个记录，则它应返回为GROUP_CONCAT(field_name).但是我的查询并没有返回我需要的方式.

I have a join query where I left join 2 tables say tab_sectorand tab_sector_subdivisions. I have records in first table which may or may not have corresponding records in 2nd table. I am joining these tables in such a way that Its should return all row from first table and matching row from 2nd table. Also if a row in first table has multiple record in 2nd table, it should return as GROUP_CONCAT(field_name). But my query doesn't return the way I need.

这是没有GROUP_CONCAT的查询:

Here is the query without GROUP_CONCAT:

SELECT tab_sector.sector_id,tab_sector.sector_title,tab_sector.sector_desc,tab_sector.sector_image,tab_sector_subdivisions.subdiv_id 
FROM tab_sector 
LEFT JOIN tab_sector_subdivisions 
ON tab_sector_subdivisions.sector_id = tab_sector.sector_id WHERE tab_sector.active = 'Y'

结果是:

您可以看到ID为20的2行.我需要的是单行，但subdiv_id为(19,20).然后，我在查询中使用了GROUP_CONCAT，例如:

you can see 2 rows for id 20. What I need is single row but subdiv_id as (19,20). Then I used GROUP_CONCAT in the query like:

    SELECT tab_sector.sector_id,tab_sector.sector_title,tab_sector.sector_desc,tab_sector.sector_image,GROUP_CONCAT(tab_sector_subdivisions.subdiv_id) 
FROM tab_sector 
LEFT JOIN tab_sector_subdivisions 
ON tab_sector_subdivisions.sector_id = tab_sector.sector_id WHERE tab_sector.active = 'Y'

那么结果将是:

我的另外两个记录丢失了.我也希望结果中包含这些行.

My other two records are missing in this. I want that rows too in my result.

有人可以帮我解决这个问题吗?预先感谢.

Can anyone please help me to solve this? Thanks in advance.

推荐答案

对于未聚合的列，您需要使用分组依据

You need the group by for the not aggregated columns

SELECT tab_sector.sector_id,tab_sector.sector_title,tab_sector.sector_desc
    ,tab_sector.sector_image,group_concat(tab_sector_subdivisions.subdiv_id )
LEFT JOIN tab_sector_subdivisions 
        ON tab_sector_subdivisions.sector_id = tab_sector.sector_id 
              AND tab_sector.active = 'Y'
GROUP BY  tab_sector.sector_id,tab_sector.sector_title,
     tab_sector.sector_desc, tab_sector.sector_image

这篇关于JOIN查询MySQL中GROUP_CONCAT的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！