sql - 选择在 SQL 中组合有 10 名或更多学生和教师的城市
问题描述
我需要显示城市、州、学生居民人数、教师居民人数以及该城市的学生/教师居民总数。该信息包含在 3 个表中:邮政编码、学生和教师。
- ZIPCODE 表具有 ZIP、CITY 和 STATE 列。
- STUDENT 表具有 STUDENT_ID 和 ZIP。
- INSTRUCTOR 表具有 INSTRUCTOR_ID 和 ZIP。
我尝试了几个内部连接和相交,但我不断收到各种各样的错误。我对 SQL 还是很陌生,不知道如何真正做到这一点,任何帮助或建议将不胜感激。
解决方案
你可能想要一个联合和加入的组合。我怀疑你想要相交。有很多方法可以做到这一点,这里有一个
SELECT
Z.city,
Z.state,
SUM(case when d.typ = 's' then 1 ELSE 0 END) as count_students,
SUM(case when d.typ = 'i' then 1 ELSE 0 END) as count_instructors,
Count(*) as count_all
FROM
(SELECT * FROM
(SELECT 's' as typ, zip FROM student)
UNION ALL
(SELECT 'I ' as typ, zip FROM Instructor)
) d
INNER JOIN
zipcode z
ON d.zip on z.zip
GROUP BY
z.city, z.state
我从每个学生和教师表中提取所有记录并将它们合并成一个大列表,创建一列来跟踪类型,总和进行计数,当类型为 s 时,返回 1 的情况。总和会将 1 加起来作为计数。因此,您最终会得到每行的城市/州/典型组合,当按城市和州分组并在典型上求和时,它会给出一个计数
这是另一种方法:
SELECT
Z.city,
Z.state,
SUM(s.ct) as count_students,
SUM(i.ct) as count_instructors,
SUM(s.ct) + SUM(I.ct) as count_all
FROM
zipcode z
LEFT OUTER JOIN
(SELECT zip, count(*) ct FROM student GROUP BY zip) s
ON s.zip = z.zip
LEFT OUTER JOIN
(SELECT zip, count(*) as ct FROM Instructor GROUP BY zip) i
ON i.zip = z.zip
GROUP BY z.city, z.state
我们在他们自己的子查询中对学生和教师进行分组和计数,每个邮政编码只产生一个计数,并将这些(左连接)加入所有邮政编码。我们在一个子查询中分组以确保邮政编码和 s/i 之间只有 1:1 的关系。如果它是 1:many,那么总和会变得扭曲。因为多个 zip 可以引用一个城市,所以需要另一轮分组和求和来聚合来自一个城市的所有 zip
推荐阅读
- php - Laravel Socialite 在谷歌回调上给出 403 禁止错误
- sql - Inner Join x Exists - 有区别吗
- java - GeoJSON:检查地理坐标是否在多边形内
- kotlin - 如何在 Kotlin 中编写 for 循环
- r - 错误'缺少参数“参数”,没有默认值' - R 中的 optim() 函数
- ruby-on-rails - 如何使用 button_to 隐藏按钮?
- jquery - 如何在jquery中检测div外的点击
- apache - Apache:无效命令“Krb5Keytab”
- r - 数据框从现有行添加新行
- c++ - 链接 pcre 时无法启用 LTO