如下表:课程表course,学生成绩表sc,求全部学生都选修的课程的课程名
create table course
(
c# int, //课程号
t# int, //课程名
cname varchar(50)
)
create table sc
(
s# int, //学生号
c# int, //课程号
score int //分数
)
//测试数据
insert into course values(1,1,'语文')
insert into course values(2,1,'数学')
insert into course values(3,2,'英语')insert into sc values(1,1,50)
insert into sc values(1,2,60)
insert into sc values(1,3,70)
insert into sc values(2,1,80)
insert into sc values(2,3,90)
insert into sc values(2,2,90)insert into sc values(3,2,50)则上面全部学生都选修的课程为课程2,数学
create table course
(
c# int, //课程号
t# int, //课程名
cname varchar(50)
)
create table sc
(
s# int, //学生号
c# int, //课程号
score int //分数
)
//测试数据
insert into course values(1,1,'语文')
insert into course values(2,1,'数学')
insert into course values(3,2,'英语')insert into sc values(1,1,50)
insert into sc values(1,2,60)
insert into sc values(1,3,70)
insert into sc values(2,1,80)
insert into sc values(2,3,90)
insert into sc values(2,2,90)insert into sc values(3,2,50)则上面全部学生都选修的课程为课程2,数学
解决方案 »
- 急求 sql
- SQL如何算出一年中有多少周及每周的日期段,设周末为第一天
- 存储过程问题!
- reporting services 如何将数据导入多个excel sheet中
- sql 中代替 in(...) 的写法.....................等....
- 如果提高20个LEFT JOIN 查询的速度??????
- 如何实现下面的查询: 比如:12,1125,45,87 对应的字段 aaa,srw,sdfw,dge
- sql server 2000的olap在那里?是Analysis services?
- 各位朋友:请问如何用VB将已存入SQLserver中的图片(二进制形式)取出来并放在一个Picture控件中
- 急!update 表变量问题@@@@
- 如何同时查询两个表,并修改查询结果的显示
- 求助高手 触发器不起作用 哪的问题?
SELECT * FROM course C
WHERE (SELECT COUNT(0) FROM sc WHERE c#=C.c#)=(SELECT COUNT(DISTINCT s#) FROM sc)
很多方法的
select * from course
where c# in
(
select c# from
(
select COUNT(s#) cnt,c# from sc
group by c#
) a
where cnt=(SELECT COUNT(DISTINCT s#) FROM sc)
)
create table course
(
c# int, --课程号
t# int, --课程名
cname varchar(50)
)
create table sc
(
s# int, --学生号
c# int, --课程号
score int --分数
)
--测试数据
insert into course values(1,1,'语文')
insert into course values(2,1,'数学')
insert into course values(3,2,'英语')insert into sc values(1,1,50)
insert into sc values(1,2,60)
insert into sc values(1,3,70)
insert into sc values(2,1,80)
insert into sc values(2,3,90)
insert into sc values(2,2,90)
insert into sc values(3,2,50)select c.c#, c.cname from course as c join sc on sc.c#=c.c# group by c.c#, c.cname
having count(1)=(select count(distinct s#) from sc)c# cname
----------- --------------------------------------------------
2 数学(1 row(s) affected)