mysql - SQL 子查询作为具有多个 where 子句的列

Question

我有三个表学校、年级、学生。表Grade 有一个外键 列s_id，该列引用主键 列id School 和表 Student 有一个引用主键的外键列g_id > 表 Grade 的列 id。我需要选择 school_name，高度低于 170 或高于 180 的学生人数。现在，我像这样编写选择查询。

SELECT School.name, School.id as sid, 
       (Select count(*) from Student inner join Grade on 
                     Student.g_id=Grade.id inner join School on 
                     Grade.s_id=School.id where School.id=sid and 
                     Student.height < 170) as under_170,  
       (Select count(*) from Student inner join Grade on 
                     Student.g_id=Grade.id inner join School on 
                     Grade.s_id=School.id where School.id=sid and Student.height > 180) as over_180
from School

我必须对每一列使用join。有更好的方法吗？

Answer 1

以下内容使用 SUM() 聚合器，MySQL 在对值求和以实现所需输出时将 bool 值视为 1 或 0 .

SELECT school.id, school.name, SUM(student.height < 170) AS under_170, SUM(student.height > 180) AS over_180
FROM school
INNER JOIN grade ON (school.id = grade.s_id)
INNER JOIN student ON (grade.id = student.g_id)
GROUP BY school.id