SELECT t2.sc_Name 学校名称,t1.[StudentsCounts] 计划招生人数,t3.Real_Num 实际招生人数,t1.[StudentsCounts]-t3.Real_Num 剩余招生人数
FROM StudentsCounts t1
INNER JOIN schools t2 ON t1.[sc_Code]=t2.[sc_Code]
INNER JOIN (SELECT [sc_Code],COUNT(*) Real_Num FROM [Students] GROUP BY [sc_Code]) t3
ON t1.[sc_Code]=t3.[sc_Code]
--修改了一下,如果某学校一个也没有招到的话也将显示出来
SELECT t2.sc_Name 学校名称,t1.[StudentsCounts] 计划招生人数,t3.Real_Num 实际招生人数,t1.[StudentsCounts]-ISNULL(t3.Real_Num,0) 剩余招生人数
FROM StudentsCounts t1
INNER JOIN schools t2 ON t1.[sc_Code]=t2.[sc_Code]
left JOIN (SELECT [sc_Code],COUNT(*) Real_Num FROM [Students] GROUP BY [sc_Code]) t3
ON t1.[sc_Code]=t3.[sc_Code]