mysql 多子查询 group_concat 查询

Question

我试图显示某个特定城镇的行政区和邮政编码。

我的数据库结构相当好，有一个表格，如城镇、邮政编码和行政区。还有每个关系town_postcode 和town_postcode 的表。镇_自治市镇。

理想情况下，我希望返回的数据为：

“Abbey Wood”、“SE2”、“Bexley，Greenwich” “Barbican”、“EC1、EC2”、“City of London”

我尝试了几种不同的方法，已经很接近了，但还没有实现。

任何帮助将不胜感激...:) 到目前为止我已经尝试过

SELECT DISTINCT t.town, 
GROUP_CONCAT( DISTINCT p.postcode SEPARATOR ', ' ) AS 'postcode', 
GROUP_CONCAT( DISTINCT b.borough SEPARATOR ', ' ) AS 'borough'
FROM coverage_towns AS t, 
coverage_boroughs AS b, 
coverage_postcodes AS p, 
coverage_towns_boroughs AS tb, 
coverage_towns_postcodes AS tp
WHERE t.id = tp.town_id
AND p.id = tp.postcode_id
AND b.id = tb.borough_id
GROUP BY t.town
ORDER BY t.town ASC

哪个返回

"Abbey Wood", "SE2", "Southwark, Hammersmith and Fulham, Tower Hamlets, Wandsworth, Enfield, Newham, LOTS MORE HERE"
"Barbican", "EC1, EC2", "Brent, Greenwich, Kensington and Chelsea, Westminster, Camden, LOTS MORE HERE"

我也尝试过

SELECT DISTINCT t.town, (

SELECT SQL_CACHE DISTINCT GROUP_CONCAT( p1.postcode
SEPARATOR ', ' )
FROM coverage_postcodes AS p1
WHERE p1.id = tp.postcode_id
) AS 'postcode', (

SELECT SQL_CACHE DISTINCT GROUP_CONCAT( b1.borough
SEPARATOR ', ' )
FROM coverage_boroughs AS b1
WHERE b1.id = tb.borough_id
) AS 'borough'
FROM coverage_towns AS t, coverage_boroughs AS b, coverage_postcodes AS p, coverage_towns_boroughs AS tb, coverage_towns_postcodes AS tp
WHERE t.id = tp.town_id
AND p.id = tp.postcode_id
AND b.id = tb.borough_id
GROUP BY t.town
ORDER BY t.town ASC

哪个返回

"Abbey Wood", "SE2", "Greenwich"
"Acton", "W3", "Greenwich"
"Aldersbrook", "E12", "Greenwich"

Answer 1

第一个查询看起来不错，只需在 group_concat 中添加 distinct 即可，例如：

SELECT  t.town
,      GROUP_CONCAT(DISTINCT p.postcode SEPARATOR ', ' ) AS 'postcode'
,      GROUP_CONCAT(DISTINCT b.borough SEPARATOR ', ' ) AS 'borough'
<more code here>
GROUP BY
        t.town

Answer 2

解决方案

喝完一杯好咖啡后，我回到了这个问题，答案自然而然地出现了。

SELECT DISTINCT t.town, 
GROUP_CONCAT( DISTINCT p.postcode SEPARATOR ', ' ) AS 'postcode', 
GROUP_CONCAT( DISTINCT b.borough SEPARATOR ', ' ) AS 'borough'
FROM towns AS t, boroughs AS b, postcodes AS p, towns_boroughs AS tb, towns_postcodes AS tp
WHERE (t.id = tp.town_id AND t.id = tb.town_id)
AND (p.id = tp.postcode_id AND b.id = tb.borough_id)
GROUP BY t.town
ORDER BY t.town ASC