php - 各区用户数均为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 子句，则 SELECT 列表中的每个列引用必须标识分组列或者是集合函数的参数。

当LEFT JOIN时，通常不会将右侧表的条件放在WHERE子句中，如果这样做会得到内连接结果。移至 ON 子句以获得真正的 LEFT JOIN 结果!