求一sql语句???

[xun56789]

stu:
stuid, stuname :学号，姓名
1 a
2 b
--------------------------
source:
stuid, sid ：学号，课程号
1 10
1 11
2 10
====================================
查询出 所有人的 学号，姓名，选学课程的个数
用一条 sql 语句

[dawugui]

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

[leo_lesley]

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

[dawugui]

create table stu(stuid int,stuname varchar(10))
insert into stu values(1, 'a')
insert into stu values(2, 'b')
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 stu.* , isnull(t.cnt,0) 选学课程的个数 from stu
left join
(
select stuid,count(*) cnt from source group by stuid
) t
on stu.stuid = t.stuid

drop table stu,source

/*
stuid stuname 选学课程的个数
----------- ---------- -----------
1 a 2
2 b 1

（所影响的行数为 2 行）

*/

[leo_lesley]

---- 例子 -----

create table stu(stuid int,stuname varchar(10))
insert into stu values(1, 'a')
insert into stu values(2, 'b')
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 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



drop table stu,source

/* 结果

stuid stuname CNT
----------- ---------- -----------
1 a 2
2 b 1

（所影响的行数为 2 行）

*/

[vchao13]

select stu.stuid,stu.stuname,count(*)as 选修课个数　 from stu,source where stu.stuid=source.stuid group by stu.stuid,stu.stuname

[simonhehe]

竞争激烈啊

[vchao13]

select stu.stuid,stu.stuname,count(*) as 选修课个数 from stu,source where stu.stuid=source.stuid group by stu.stuid,stu.stuname

[xun56789]

谢谢各位！！！
＝＝＝＝＝＝＝＝＝＝＝
source:
stuid, sid ：学号，课程号
1 10
1 11
－－2 10－－去掉该记录
==================＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
这样的话：

stuid stuname CNT
----------- ---------- -----------
1 a 2
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
而 2 b 0
确不存在

[xun56789]

我想
stuid stuname
----------- ---------- -----------
1 a 2
2 b 0
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
就是说他没选课，选课数就为0

[lost_queen]

高手们都很热心啊～
赞的！

[anison]

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

[anison]

借用了1楼乌龟大哥的测试数据~~谢谢

[senaku]

CREATE TABLE stu
(
stuid INT,
stuname VARCHAR(20)
)
GO
INSERT INTO stu VALUES(1, 'a')
INSERT INTO stu VALUES(2, 'b')
INSERT INTO stu VALUES(3, 'c')
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 t.stuid, t.stuname, sid_total=COUNT(s.sid) FROM stu t LEFT JOIN source s ON t.stuid = s.stuid
GROUP BY t.stuid, t.stuname

[senaku]

stuid stuname sid_total
----------- -------------------- -----------
1 a 2
2 b 1
3 c 0

[woaiguainv]

一个连接查询就可以：select * from stu a, (select stuid count(sid) as cnumber group by stuid from source) b
on a.stuid=b.stuid

[woaiguainv]

select * from stu a,(select studi count(sid) as cnumber group by stuid from source) b
where a.stuid=b.stuid