咨询一个SQL查询语句与视图的问题

[ZaiJianLiXiang2012]

例如有A\B两个表，
其中A表为“班级表”，有：班级编号、班级名称、教室号、班主任等字段信息。
B表为“学生表”，有： 班级编号、学生名字、性别、年龄等字段信息。
现在想以“班级表”为主，形成一个视图，增加一个“学生数量”字段。请问如何用SQL语句实现？
如：班级编号、班级名称、教室号、班主任、学生数量

[吉普赛的歌]

USE tempdb
GO
IF OBJECT_ID('view_test') IS NOT NULL DROP VIEW view_test;
IF OBJECT_ID('A_Class') IS NOT NULL DROP TABLE A_Class;
IF OBJECT_ID('B_Student') IS NOT NULL DROP TABLE B_Student;
GO
CREATE TABLE A_Class(
	classId INT PRIMARY KEY
	,className NVARCHAR(10)
	,roomNo NVARCHAR(10)
	,managerName NVARCHAR(10)
)
CREATE TABLE B_Student(
	classId INT,
	stuName NVARCHAR(10),
	gender NCHAR(1),
	age INT	
)
GO
INSERT INTO A_Class(classId,className,roomNo,managerName) VALUES(1,'一（1）班','301','李小刚')
INSERT INTO A_Class(classId,className,roomNo,managerName) VALUES(2,'一（2）班','302','王芬')
INSERT INTO A_Class(classId,className,roomNo,managerName) VALUES(3,'一（3）班','303','王力宏')
INSERT INTO B_Student(classId,stuName,gender,age) VALUES (1,'小明','男',10)
INSERT INTO B_Student(classId,stuName,gender,age) VALUES (1,'小张','男',10)
INSERT INTO B_Student(classId,stuName,gender,age) VALUES (2,'小华','女',10)
INSERT INTO B_Student(classId,stuName,gender,age) VALUES (2,'小李','女',10)
INSERT INTO B_Student(classId,stuName,gender,age) VALUES (2,'小陈','男',10)
GO
----------- 以上为测试表及测试数据 ----------------------

--创建视图
CREATE VIEW dbo.view_test
AS 
SELECT a.classId
,a.className
,a.roomNo
,a.managerName
,COUNT(b.classId) AS stuCnt
FROM dbo.A_Class AS a LEFT JOIN dbo.B_Student AS b ON a.classId=b.classId
GROUP BY a.classId
,a.className
,a.roomNo
,a.managerName
GO

--查询视图
SELECT * FROM view_test
/*
classId     className  roomNo     managerName stuCnt
----------- ---------- ---------- ----------- -----------
1           一（1）班      301        李小刚        2
2           一（2）班      302        王芬          3
3           一（3）班      303        王力宏        0
*/

[ZaiJianLiXiang2012]

引用 1 楼 yenange 的回复:

USE tempdb
GO
IF OBJECT_ID('view_test') IS NOT NULL DROP VIEW view_test;
IF OBJECT_ID('A_Class') IS NOT NULL DROP TABLE A_Class;
IF OBJECT_ID('B_Student') IS NOT NULL DROP TABLE B_Student;
GO
CREATE TABLE A_Class(
	classId INT PRIMARY KEY
	,className NVARCHAR(10)
	,roomNo NVARCHAR(10)
	,managerName NVARCHAR(10)
)
CREATE TABLE B_Student(
	classId INT,
	stuName NVARCHAR(10),
	gender NCHAR(1),
	age INT	
)
GO
INSERT INTO A_Class(classId,className,roomNo,managerName) VALUES(1,'一（1）班','301','李小刚')
INSERT INTO A_Class(classId,className,roomNo,managerName) VALUES(2,'一（2）班','302','王芬')
INSERT INTO A_Class(classId,className,roomNo,managerName) VALUES(3,'一（3）班','303','王力宏')
INSERT INTO B_Student(classId,stuName,gender,age) VALUES (1,'小明','男',10)
INSERT INTO B_Student(classId,stuName,gender,age) VALUES (1,'小张','男',10)
INSERT INTO B_Student(classId,stuName,gender,age) VALUES (2,'小华','女',10)
INSERT INTO B_Student(classId,stuName,gender,age) VALUES (2,'小李','女',10)
INSERT INTO B_Student(classId,stuName,gender,age) VALUES (2,'小陈','男',10)
GO
----------- 以上为测试表及测试数据 ----------------------

--创建视图
CREATE VIEW dbo.view_test
AS 
SELECT a.classId
,a.className
,a.roomNo
,a.managerName
,COUNT(b.classId) AS stuCnt
FROM dbo.A_Class AS a LEFT JOIN dbo.B_Student AS b ON a.classId=b.classId
GROUP BY a.classId
,a.className
,a.roomNo
,a.managerName
GO

--查询视图
SELECT * FROM view_test
/*
classId     className  roomNo     managerName stuCnt
----------- ---------- ---------- ----------- -----------
1           一（1）班      301        李小刚        2
2           一（2）班      302        王芬          3
3           一（3）班      303        王力宏        0
*/

非常更新版主的解答，我用另一种方式也实现了。

FROM dbo.A LEFT OUTER JOIN
(SELECT ID, COUNT(B_NO) AS CountNuber
FROM dbo.B
GROUP BY ID) AS temp ON dbo.A.ID = temp.ID

[吉普赛的歌]

引用 2 楼 ZaiJianLiXiang2012 的回复:

非常感谢版主的解答，我用另一种方式也实现了。

FROM dbo.A LEFT OUTER JOIN
(SELECT ID, COUNT(B_NO) AS CountNuber
FROM dbo.B
GROUP BY ID) AS temp ON dbo.A.ID = temp.ID

不客气。方法有很多种， 下面也可以的：

CREATE VIEW dbo.view_test
AS 
SELECT a.classId
,a.className
,a.roomNo
,a.managerName
,(SELECT COUNT(b.classId) FROM dbo.B_Student AS b WHERE a.classId=b.classId) AS stuCnt
FROM dbo.A_Class AS a 

没事就结贴吧。