drwg89980 2015-12-02 09:06
浏览 33
已采纳

每个区的用户数包括0计数

I need to find the number of users in every district.

My DB Structure:

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 table contain more than one address for a user. But only one is active. I need to find number of users in a district and country.

I used this query:

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

I get all the districts in the user_addresses and their count. But still I have some districts in districts table for which I need to display the count as 0.

What I get:

country_name    district_name   COUNT(*)
India           Trichy          1
India           Chennai         2
United States   Phoenix         1

What I need:

country_name    district_name   COUNT(*)
India           Trichy          1
India           Chennai         2
India           Hyderabad       0
Singapore       ...             0
Malaysia        ...             0
...             ...             0
...             ...             0
United States   Phoenix         1
  • 写回答

1条回答 默认 最新

  • dongzou3751 2015-12-02 09:09
    关注
    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
    

    The general GROUP BY rule says:

    If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function.

    When LEFT JOIN, you usually don't put right side table's conditions in the WHERE clause, if you do you get inner join result. Move to ON clause to get true LEFT JOIN result!

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 用前端向数据库插入数据,通过debug发现数据能走到后端,但是放行之后就会提示错误
  • ¥15 python天天向上类似问题,但没有清零
  • ¥30 3天&7天&&15天&销量如何统计同一行
  • ¥30 帮我写一段可以读取LD2450数据并计算距离的Arduino代码
  • ¥15 C#调用python代码(python带有库)
  • ¥15 矩阵加法的规则是两个矩阵中对应位置的数的绝对值进行加和
  • ¥15 活动选择题。最多可以参加几个项目?
  • ¥15 飞机曲面部件如机翼,壁板等具体的孔位模型
  • ¥15 vs2019中数据导出问题
  • ¥20 云服务Linux系统TCP-MSS值修改?