使用SQL中的连接查询，使用第三个表从两个不同的表中检索两列的计数

I have 3 tables: COUNTRY, STATE, CITY

我有3个表:COUNTRY,STATE,CITY

This is my Country table with two columns:

这是我的Country表,有两列:

CountryID, Name

This is my State table:

这是我的状态表:

This is my City table:

这是我的城市表:

I want to retrieve the count of states and cities according to the country table using join query.

我想使用连接查询根据国家/地区表检索州和城市的数量。

1 个解决方案

#1

Skipping the fact that your question is not asked well - try this query, it should work for you:

跳过你的问题没问得好的事实 - 尝试这个查询,它应该适合你:

WITH
tab_a AS (
SELECT c.countryid, COUNT (s.stateid) AS state_num
FROM country c
LEFT JOIN state s ON c.countryid = s.countryid
GROUP BY c.countryid
),
tab_b AS (
SELECT c.countryid, COUNT (cc.cityid) city_num
FROM country c
LEFT JOIN state s ON c.countryid = s.countryid
LEFT JOIN city cc ON s.stateid = cc.stateid
GROUP BY c.countryid
)
SELECT a.countryid,
       a.state_num,
       b.city_num
FROM tab_a a JOIN tab_b b ON a.countryid=b.countryid