查询状态城市 [英] query on states & cities

问题描述

我有两个表:states和cities

i have two tables: states and cities

states
----------
 - stateid
 - statename

cities
--------
 - cityid
 - cityname
 - stateid

我想显示该州没有城市的州名

i want display statename with no of cities in that state

statename    count(cities)
---------    -----
keral          5
chennai        10

有人可以告诉我sql查询以获取结果吗?

Can any one tell me the sql query to get the result?

推荐答案

就像这样:

Like this:

SELECT s.statename, COUNT(c.cityid) AS CountOfCities
FROM cities as c LEFT JOIN states as s ON c.stateid = s.stateid
GROUP BY s.statename

正如曼弗雷德·R·比希(Manfred R. Bihy)在评论中写道，如果您想获得所有州和城市，即使州没有城市-可能也不可能;)-您需要使用如下查询:

As Manfred R. Bihy wrote in comment, if you would like to get all states and cities, even if state has no city - probably it''s impossible ;) - you need to use query like this:

SELECT s.statename, COUNT(c.cityid) AS CountOfCities
FROM states as s LEFT JOIN cities as c ON s.stateid = c.stateid
GROUP BY s.statename

谢谢Manfred;)

Thank you Manfred ;)

请尝试以下SQL语句:

Please try the following SQL statement:

SELECT st.StateName, COUNT(cy.cityid) as NoOfCities
FROM states st LEFT JOIN cities cy ON st.StateId == cy.StateId
GROUP BY st.StateName

问候，

曼弗雷德(Manfred)

Regards,

Manfred

这篇关于查询状态城市的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！