我需要找到每个地区的用户数量。
我的数据库结构:
Table Name : countries
country_id country_name
1 India
2 Singapore
3 Malaysia
4 United States
Table Name : states
state_id state_name country_id
1 Tamil Nadu 1
2 Andhra 1
.
.
.
20 Arizona 4
Table Name : districts
district_id district_name state_id
1 Trichy 1
2 Chennai 1
3 Hyderabad 2
.
.
.
100 Phoenix 20
Table Name : user_addresses
user_address_id user_id doorno street ... districtName stateName countryName active
1 1 10 .... ... Trichy Tamil Nadu India 1
2 2 A12 .... ... Chennai Tamil Nadu India 0
3 3 A2 .... ... Chennai Tamil Nadu India 1
4 4 B2 .... ... Chennai Tamil Nadu India 1
5 41 89 .... ... Phoenix Arizona United States 1
user_addresses
表包含一个用户的多个地址。但只有一个是活跃的。我需要查找一个地区和国家的用户数量。
我使用了这个查询:
SELECT country_name, district_name, COUNT(*)
FROM districts as d
LEFT JOIN states as s USING (state_id)
LEFT JOIN countries as c USING (country_id)
LEFT JOIN user_addresses as ua on ua.districtName = d.district_name
WHERE ua.active=1
GROUP BY ua.district
我得到了user_addresses
中的所有地区及其计数。但我仍然在districts
表中有一些地区,我需要将其计数显示为0。
我得到的:
country_name district_name COUNT(*)
India Trichy 1
India Chennai 2
United States Phoenix 1
我需要什么:
country_name district_name COUNT(*)
India Trichy 1
India Chennai 2
India Hyderabad 0
Singapore ... 0
Malaysia ... 0
... ... 0
... ... 0
United States Phoenix 1
SELECT country_name, district_name, COUNT(ua.active)
FROM districts as d
LEFT JOIN states as s USING (state_id)
LEFT JOIN countries as c USING (country_id)
LEFT JOIN user_addresses as ua on ua.districtName = d.district_name
AND ua.active=1
GROUP BY country_name, district_name
一般的GROUP BY
规则说:
如果指定了GROUP BY子句,SELECT列表中的每个列引用都必须标识一个分组列或是集合函数的参数
当使用LEFT JOIN
时,通常不会将右侧表的条件放在WHERE
子句中,如果这样做,则会得到内部联接结果。移动到ON
子句以获得真正的LEFT JOIN
结果!