用户数在每个地区包括0计数

Question

我需要找到每个地区的用户数量。

我的数据库结构：

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 

Answer 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 cla指定使用时，SELECT列表中的每个列引用必须标识一个分组列或者是一个set函数的参数。

当LEFT JOIN，如果你得到内部连接结果，你通常不会在WHERE子句中放置右侧表的条件。移至ON子句得到结果为真LEFT JOIN！