我在MySQL中有Cities
、Countries
和Customers
表。
Cities: city_id, city_name, ...
Counties: county_id, city_id, county_name, ...
Customers: cus_id, city_id, county_id, ...
与其在Country
中列出所有城市(以及城市下的县),我只想列出Cities
和Customers
注册的县。
这里有MySQL专家帮我吗?非常感谢。当做
您的表Customers
未正确规范化。此表中不应该有列city_id
,因为county_id => city_id
(给定county_id
,可以找到city_id
)。
我不确定我是否正确理解了你(如果没有,请更好地解释提供样本数据),但对于你想要的,我认为你可以使用以下查询:
SELECT aa.city_name, bb.county_name
FROM Cities AS aa
INNER JOIN Counties AS bb
ON aa.city_id = bb.city_id
INNER JOIN Customers AS cc
ON bb.county_id = cc.county_id;
Select UNIQUE city_id from Customers
JOIN Cities
ON Customers.city_id=Cities.city_id;
我只是转述一下,但我希望表明我的观点。