22,210
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据:[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
*/
--> 测试数据:[ 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'
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
*/
--> 测试数据:[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
*/