select s.sno,s.sname,count(cno) from student s left join 选课 b on s.sno=b.sno right join course c on b.cno=c.cno group by s.sno,s.sname
不过貌似你的逻辑有点问题吧?一个学生选了多少个课,和课程表中的数量有什么关系?select s.sno,s.sname,count(cno)[选课数量],(select count(1) from course) [总课数] from student s left join 选课 b on s.sno=b.sno right join course c on b.cno=c.cno group by s.sno,s.sname这样可以不?
--> 测试数据:[student] if object_id('[student]') is not null drop table [student] go create table [student]([sno] varchar(4),[sname] varchar(4)) insert [student] select '0001','张三' union all select '0002','李四' union all select '0003','xxxx'--> 测试数据:[ Course] if object_id('[Course]') is not null drop table [Course] go create table [Course]([cno] varchar(3),[cname] varchar(4)) insert [Course] select '001','语文' union all select '002','数学' union all select '003','英语'--> 测试数据:[选课表] if object_id('[选课表]') is not null drop table [选课表] go create table [选课表]([sno] varchar(4),[cno] varchar(3)) insert [选课表] select '0001','001' union all select '0001','002' union all select '0001','003' union all select '0002','001' union all select '0002','002'SELECT s.sno , s.sname , COUNT(b.cno) [选课数量] , ( SELECT COUNT(1) FROM course ) [总课数] FROM student s LEFT JOIN [选课表] b ON s.sno = b.sno RIGHT JOIN course c ON b.cno = c.cno GROUP BY s.sno , s.sname/* sno sname 选课数量 总课数 ---- ----- ----------- ----------- 0002 李四 2 3 0001 张三 3 3 */
--> 测试数据:[student] if object_id('[student]') is not null drop table [student] go create table [student]([sno] varchar(4),[sname] varchar(4)) insert [student] select '0001','张三' union all select '0002','李四' union all select '0003','xxxx'
--> 测试数据:[ Course] if object_id('[Course]') is not null drop table [Course] go create table [Course]([cno] varchar(3),[cname] varchar(4)) insert [Course] select '001','语文' union all select '002','数学' union all select '003','英语'
--> 测试数据:[选课表] if object_id('[选课表]') is not null drop table [选课表] go create table [选课表]([sno] varchar(4),[cno] varchar(3)) insert [选课表] select '0001','001' union all select '0001','002' union all select '0001','003' union all select '0002','001' union all select '0002','002'
SELECT s.sno , s.sname , COUNT(b.cno) [选课数量] , ( SELECT COUNT(1) FROM course ) [总课数] FROM student s LEFT JOIN [选课表] b ON s.sno = b.sno RIGHT JOIN course c ON b.cno = c.cno GROUP BY s.sno , s.sname HAVING COUNT(b.cno)=( SELECT COUNT(1) FROM course ) /* sno sname 选课数量 总课数 ---- ----- ----------- ----------- 0001 张三 3 3 */
谢谢版主啦,。 既然问啦, 那我也再多问一下。。 如果把你上面的数据改下就不对啦, --> 测试数据:[ Course] if object_id('[Course]') is not null drop table [Course] go create table [Course]([cno] varchar(3),[cname] varchar(4)) insert [Course] select '001','语文' union all select '002','数学' union all select '003','英语'
--> 测试数据:[选课表] if object_id('[选课表]') is not null drop table [选课表] go create table [选课表]([sno] varchar(4),[cno] varchar(3)) insert [选课表] select '0001','001' union all select '0001','002' union all select '0001','003' union all select '0002','001' union all select '0002','002' union all select '0002','004' union all select '0002','002'多添加了两条数据, 其中有一个是004,这个在课程表中不存在,(可能存在这种情况)。版主大人,如果是这样怎么破
是不是要这样?--> 测试数据:[student] if object_id('[student]') is not null drop table [student] go create table [student]([sno] varchar(4),[sname] varchar(4)) insert [student] select '0001','张三' union all select '0002','李四' union all select '0003','xxxx' --> 测试数据:[ Course] if object_id('[Course]') is not null drop table [Course] go create table [Course]([cno] varchar(3),[cname] varchar(4)) insert [Course] select '001','语文' union all select '002','数学' union all select '003','英语'
--> 测试数据:[选课表] if object_id('[选课表]') is not null drop table [选课表] go create table [选课表]([sno] varchar(4),[cno] varchar(3)) insert [选课表] select '0001','001' union all select '0001','002' union all select '0001','003' union all select '0002','001' union all select '0002','002' union all select '0002','004' union all select '0002','002'SELECT s.sno , s.sname , COUNT(b.cno) [选课数量] , ( SELECT COUNT(1) FROM course ) [总课数] FROM student s RIGHT JOIN [选课表] b ON s.sno = b.sno LEFT JOIN course c ON b.cno = c.cno GROUP BY s.sno , s.sname HAVING COUNT(b.cno)=( SELECT COUNT(1) FROM course ) /* sno sname 选课数量 总课数 ---- ----- ----------- ----------- 0001 张三 3 3 */
from student s left join 选课 b on s.sno=b.sno
right join course c on b.cno=c.cno
group by s.sno,s.sname
from student s left join 选课 b on s.sno=b.sno
right join course c on b.cno=c.cno
group by s.sno,s.sname这样可以不?
if object_id('[student]') is not null drop table [student]
go
create table [student]([sno] varchar(4),[sname] varchar(4))
insert [student]
select '0001','张三' union all
select '0002','李四' union all
select '0003','xxxx'--> 测试数据:[ Course]
if object_id('[Course]') is not null drop table [Course]
go
create table [Course]([cno] varchar(3),[cname] varchar(4))
insert [Course]
select '001','语文' union all
select '002','数学' union all
select '003','英语'--> 测试数据:[选课表]
if object_id('[选课表]') is not null drop table [选课表]
go
create table [选课表]([sno] varchar(4),[cno] varchar(3))
insert [选课表]
select '0001','001' union all
select '0001','002' union all
select '0001','003' union all
select '0002','001' union all
select '0002','002'SELECT s.sno ,
s.sname ,
COUNT(b.cno) [选课数量] ,
( SELECT COUNT(1)
FROM course
) [总课数]
FROM student s
LEFT JOIN [选课表] b ON s.sno = b.sno
RIGHT JOIN course c ON b.cno = c.cno
GROUP BY s.sno ,
s.sname/*
sno sname 选课数量 总课数
---- ----- ----------- -----------
0002 李四 2 3
0001 张三 3 3
*/
if object_id('[student]') is not null drop table [student]
go
create table [student]([sno] varchar(4),[sname] varchar(4))
insert [student]
select '0001','张三' union all
select '0002','李四' union all
select '0003','xxxx'
--> 测试数据:[ Course]
if object_id('[Course]') is not null drop table [Course]
go
create table [Course]([cno] varchar(3),[cname] varchar(4))
insert [Course]
select '001','语文' union all
select '002','数学' union all
select '003','英语'
--> 测试数据:[选课表]
if object_id('[选课表]') is not null drop table [选课表]
go
create table [选课表]([sno] varchar(4),[cno] varchar(3))
insert [选课表]
select '0001','001' union all
select '0001','002' union all
select '0001','003' union all
select '0002','001' union all
select '0002','002'
SELECT s.sno ,
s.sname ,
COUNT(b.cno) [选课数量] ,
( SELECT COUNT(1)
FROM course
) [总课数]
FROM student s
LEFT JOIN [选课表] b ON s.sno = b.sno
RIGHT JOIN course c ON b.cno = c.cno
GROUP BY s.sno ,
s.sname
HAVING COUNT(b.cno)=( SELECT COUNT(1)
FROM course
)
/*
sno sname 选课数量 总课数
---- ----- ----------- -----------
0001 张三 3 3
*/
所以请教各位。有没有更好的办法。没看明白是什么意思
就是想
查询选修了全部课程的学生的姓名,以上面数据为例,选修了全部课程的就只有张三。
---下面的可以忽略
,本来想用group by 查询选课表每个人的count(1) 与 课程表count,判断是否相等,但因为
两个表没有外键关联,有可能存在选课表录入错误, 比如,选课表有004(地理),所以被难倒啦
既然问啦, 那我也再多问一下。。
如果把你上面的数据改下就不对啦, --> 测试数据:[ Course]
if object_id('[Course]') is not null drop table [Course]
go
create table [Course]([cno] varchar(3),[cname] varchar(4))
insert [Course]
select '001','语文' union all
select '002','数学' union all
select '003','英语'
--> 测试数据:[选课表]
if object_id('[选课表]') is not null drop table [选课表]
go
create table [选课表]([sno] varchar(4),[cno] varchar(3))
insert [选课表]
select '0001','001' union all
select '0001','002' union all
select '0001','003' union all
select '0002','001' union all
select '0002','002' union all
select '0002','004' union all
select '0002','002'多添加了两条数据, 其中有一个是004,这个在课程表中不存在,(可能存在这种情况)。版主大人,如果是这样怎么破
if object_id('[student]') is not null drop table [student]
go
create table [student]([sno] varchar(4),[sname] varchar(4))
insert [student]
select '0001','张三' union all
select '0002','李四' union all
select '0003','xxxx'
--> 测试数据:[ Course]
if object_id('[Course]') is not null drop table [Course]
go
create table [Course]([cno] varchar(3),[cname] varchar(4))
insert [Course]
select '001','语文' union all
select '002','数学' union all
select '003','英语'
--> 测试数据:[选课表]
if object_id('[选课表]') is not null drop table [选课表]
go
create table [选课表]([sno] varchar(4),[cno] varchar(3))
insert [选课表]
select '0001','001' union all
select '0001','002' union all
select '0001','003' union all
select '0002','001' union all
select '0002','002' union all
select '0002','004' union all
select '0002','002'SELECT s.sno ,
s.sname ,
COUNT(b.cno) [选课数量] ,
( SELECT COUNT(1)
FROM course
) [总课数]
FROM student s
RIGHT JOIN [选课表] b ON s.sno = b.sno
LEFT JOIN course c ON b.cno = c.cno
GROUP BY s.sno ,
s.sname
HAVING COUNT(b.cno)=( SELECT COUNT(1)
FROM course
) /*
sno sname 选课数量 总课数
---- ----- ----------- -----------
0001 张三 3 3 */