mysql - SQL根据两列返回不同的行

Question

我有以下查询。是的，我知道，我没有发布架构，但所有连接都很好，我认为它不相关。

如何返回不同 counties.name 和 cities.states_id 的所有行。

我以前使用过 distinct，但从未使用过两列。

谢谢

SELECT c.name AS id, CONCAT(c.name,", ",ci.states_id) AS value
FROM counties AS c
INNER JOIN zipcodes AS z ON z.counties_id=c.id
INNER JOIN cities_has_zipcodes AS chz ON chz.zipcodes_id=z.id
INNER JOIN cities AS ci ON ci.id=chz.cities_id
WHERE c.name LIKE "San Fr"
ORDER BY id LIMIT 100

Answer 1

你想要一个 GROUP_CONCAT函数和 GROUP BY 而不是 DISTINCT

SELECT c.name AS id, GROUP_CONCAT(c.name, ', ', ci.states_id SEPARATOR ', ') AS value
FROM counties AS c
INNER JOIN zipcodes AS z ON z.counties_id=c.id
INNER JOIN cities_has_zipcodes AS chz ON chz.zipcodes_id=z.id
INNER JOIN cities AS ci ON ci.id=chz.cities_id
WHERE c.name LIKE "San Fr"
GROUP BY c.name, ci.states_id
ORDER BY id LIMIT 100