stu:
stuid, stuname :学号，姓名
1 a
2 b
--------------------------
source:
stuid, sid ：学号，课程号
1 10
1 11
2 10
====================================
查询出 所有人的 学号，姓名，选学课程的个数
用一条 sql 语句
------解决方案--------------------
select stu.* , isnull(t.cnt,0) 选学课程的个数 from stu
left join
(
select stuid,count(*) cnt from source group by stuid
) t
on stu.stuid = t.stuid
------解决方案--------------------
SELECT A.stuid, A.stuname,B.CNT
FROM STU A inner join (select stuid, count(sid) cnt from source group by stuid ) b on a.stuid=b.stuid
------解决方案--------------------
select stu.stuid,stu.stuname,count(*) as 选修课个数 from stu,source where stu.stuid=source.stuid group by stu.stuid,stu.stuname
------解决方案--------------------
if exists(select * from sysobjects where name= 'stu ')
drop table stu
go
create table stu(stuid int,stuname varchar(10))
insert into stu values(1, 'a ')
insert into stu values(2, 'b ')
insert into stu values(3, 'c ')
go
if exists(select * from sysobjects where name= 'source ')
drop table source
go
create table source(stuid int, sid int)
insert into source values(1, 10)
insert into source values(1, 11)
insert into source values(2, 10)
go
--select * from stu
--select * from source
select a.stuid,stuname,选课门数=sum(case when sid> 0 then 1 else 0 end)
from stu a left join source b on a.stuid=b.stuid group by a.stuid,stuname
结果:
stuid stuname 选课门数
----------- ---------- -----------
1 a 2
2 b 1
3 c 0
------解决方案--------------------
stuid stuname sid_total
----------- -------------------- -----------
1 a 2
2 b 1
3 c 0
------解决方案--------------------
一个连接查询就可以：select * from stu a, (select stuid count(sid) as cnumber group by stuid from source) b
on a.stuid=b.stuid