如果一个人只能加入一个组队,可以在你的语句修改:SELECT inst, SUM(CASE WHEN team.student_code IS NULL THEN 0 ELSE 1 END) AS 人数
FROM student LEFT JOIN team
ON student.student_code = team.student_code
GROUP BY inst否则,应该:SELECT DISTINCT inst,ISNULL((SELECT COUNT(*) FROM team WHERE student_code = student.student_code),0) AS 人数
FROM student
FROM student LEFT JOIN team
ON student.student_code = team.student_code
GROUP BY inst否则,应该:SELECT DISTINCT inst,ISNULL((SELECT COUNT(*) FROM team WHERE student_code = student.student_code),0) AS 人数
FROM student
FROM student
FROM student LEFT OUTER JOIN team
ON student.student_code =team.student_code
GROUP BY inst,student.student_code
select a.* from
(SELECT inst, count(*) quantity
FROM student, team
WHERE student.student_code = team.student_code
GROUP BY inst
union all
select inst,quantity=0 from student where inst not in (SELECT inst
FROM student, team
WHERE student.student_code = team.student_code
) ) a