如果我有两个表：
学校：
school_id | class_id | school_location
-------------------------------------------
400 50 Arizona
员工：
staff_id | forename | school_id | wage
------------------------------------------
1 Peter 400 5000
我如何获得输出结果，说明在学校工作的员工人数和每所学校的薪水。
例如：
school_id | numberofstaff | salary
---------------------------------------
400 | 1 | 5000
我知道我应该像这样加入表格：
SELECT school_id
FROM school
INNER JOIN staff
ON school.school_id = staff.school_id
但是我不确定如何执行查询的其余部分。
SELECT numberofstaff COUNT(numberofstaff) from staff
GROUP BY school_id
Union all
select 'SUM' numberofstaff, COUNT(numberofstaff)
FROM staff
这应该给我我的学校ID，以及每个school_id的工作人员人数。
最佳答案
加入这两个表后，您就可以对教职员工进行计数，并对每所学校的所有工资求和：
SELECT sc.school_id,
COUNT(*) as numberofstaff,
SUM(st.wage) as salary
FROM school sc
INNER JOIN staff st
ON sc.school_id = st.school_id
GROUP BY sc.school_id