SQL查询LEFT JOIN问题（MySQL）

发布于 2024-09-29 09:23:21 字数 803 浏览 3 评论 0原文

我有 2 个表

SCHOOLS（ID、SCHOOL_NAME、CITY_ID）
学生（ID、STUDENT_NAME、SCHOOL_ID）。

我想列出特定城市的学校以及学生人数（学校名称|学生人数）

一种方法是使用相关子查询 -

select sh.school_name, 
       (select count(*) 
          from student 
         where school_id = sh.id) as student_count 
 from schools sh 
where sh.city_id = 1

但由于不建议使用相关查询，我想避免这种情况;我尝试了分组方式 -

   select sh.school_name, 
          count(st.school_id) as student_count 
     from schools sh 
left join students st on sh.id = st.school_id 
    where sh.city_id = 1 
 group by st.school_id

现在，只有当学校的学生人数 > 时，这才有效。 0.所以我猜测左连接概念不起作用，这意味着如果任何没有学生的学校应该被列为student_count = 0，但这种情况没有发生。有趣的是 - 我确实看到了 Student_count = 0 的“一条”记录，但仅此而已。

我的 LEFT JOIN 查询有什么问题吗？

原文

I have 2 tables

SCHOOLS (ID, SCHOOL_NAME, CITY_ID)
STUDENTS (ID, STUDENT_NAME, SCHOOL_ID).

I want to list schools in a particular city along with the student count (School Name| Student Count)

One way to do this is to have co-related subquery -

select sh.school_name, 
       (select count(*) 
          from student 
         where school_id = sh.id) as student_count 
 from schools sh 
where sh.city_id = 1

But since co-related queries are not recommended, I want to avoid that & I tried a group by -

   select sh.school_name, 
          count(st.school_id) as student_count 
     from schools sh 
left join students st on sh.id = st.school_id 
    where sh.city_id = 1 
 group by st.school_id

Now this works only if the student count for a school is > 0. So i m guessing the left join concept is not working, meaning if any schools that has no students should be listed as student_count=0, but that is not happening. The interesting thing is - i do see "one" record with student_count = 0, but thats about it.

Anything wrong in my LEFT JOIN query??

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

空心↖ 2024-10-06 09:23:21

用途：

   SELECT sh.id,
          sh.school_name, 
          COUNT(st.school_id) as student_count 
     FROM SCHOOLS sh 
LEFT JOIN STUDENTS st on sh.id = st.school_id  
    WHERE sh.city_id = 1 
 GROUP BY sh.id, sh.school_name

由于使用 LEFT JOIN，如果没有与学校关联的学生，则对 st 的引用将为 NULL。 COUNT 不计算 NULL 值，因此如果没有关联的学生，则 student_count 将为零。

Use:

   SELECT sh.id,
          sh.school_name, 
          COUNT(st.school_id) as student_count 
     FROM SCHOOLS sh 
LEFT JOIN STUDENTS st on sh.id = st.school_id  
    WHERE sh.city_id = 1 
 GROUP BY sh.id, sh.school_name

Because of using the LEFT JOIN, references to st will be NULL if there are no students associated with the school. COUNT doesn't count NULL values, so if there are no students associated then student_count will be zero.

回复收藏 0 原文