stu:
stuid, stuname :学号,姓名
1 a
2 b
--------------------------
source:
stuid, sid :学号,课程号
1 10
1 11
2 10
====================================
查询出 所有人的 学号,姓名,选学课程的个数
用一条 sql 语句
stuid, stuname :学号,姓名
1 a
2 b
--------------------------
source:
stuid, sid :学号,课程号
1 10
1 11
2 10
====================================
查询出 所有人的 学号,姓名,选学课程的个数
用一条 sql 语句
解决方案 »
- 续小问题
- 帮忙优化一下,这句SQL。
- 谁能帮助接一下这个帖子阿~关于存储过程的问题,在线等~~~~~
- 在XP下刚安装的sql server 2000,怎么无法启动服务管理器?
- 求一个多行转为一行显示的sql--在线等!!!
- 怎样得到当前数据库的所有备份文件列表和备份时间?好像是使用backupfile,backupmediafamily,backupmediaset等几个系统表
- 求sum和avg,急
- 多条数据同时触发
- 为什么安装SQLSERVER 2000后不能在命令行使用某些DOS命令了(ping,netstat,ipconfig等)
- sql server 中和case该如何表达....
- SQL Server 2005 Service Broker 用过的请进
- 存储过程中,语句中表名是上一个select搜出来的,老出错!!!
left join
(
select stuid,count(*) cnt from source group by stuid
) t
on stu.stuid = t.stuid
FROM STU A inner join (select stuid, count(sid) cnt from source group by stuid ) b on a.stuid=b.stuid
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.stuiddrop table stu,source/*
stuid stuname 选学课程的个数
----------- ---------- -----------
1 a 2
2 b 1(所影响的行数为 2 行)*/
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.stuiddrop table stu,source/* 结果 stuid stuname CNT
----------- ---------- -----------
1 a 2
2 b 1(所影响的行数为 2 行)*/
===========
source:
stuid, sid :学号,课程号
1 10
1 11
--2 10--去掉该记录
=================================
这样的话:stuid stuname CNT
----------- ---------- -----------
1 a 2
=================
而 2 b 0
确不存在
stuid stuname
----------- ---------- -----------
1 a 2
2 b 0
===========================
就是说他没选课,选课数就为0
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 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
----------- -------------------- -----------
1 a 2
2 b 1
3 c 0
on a.stuid=b.stuid
where a.stuid=b.stuid