sql子查询作为具有多个where-caluse的列

tp5buhyn 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(257)

我有三张table School, Grade, Student . 表 Grade 有一个 Foreign Key 列 s_id 上面提到的 Primary Key 列 id 的 School 和table Student 有一个 Foreign Key 列 g_id 上面提到的 Primary Key 列 id 表的 Grade . 我需要选择学校名称，身高低于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 有没有更好的方法？

sql mysql Join subquery

来源：https://stackoverflow.com/questions/51165795/sql-subquery-as-column-with-multiple-where-caluse

1条答案

按热度按时间

vom3gejh1#

以下使用 SUM() 聚合器，mysql将布尔值视为 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

赞(0）回复(0）举报 2021-06-20

我来回答

sql子查询作为具有多个where-caluse的列

1条答案

相关问题

热门标签

最新问答