一个笔试问题

[Apq001]

如下一表，表示某个同学选择了某些课程，用SQL如何查询出
“选择了 ‘B’同学所选择的所有课程的 学生的Name”？

s_c:
Name Course
A 语文
A 数学
A 英语
B 语文
B 英语
C 数学
C 英语

按题意，结果应该是
Name
A

求该SQL语句。

[冷箫轻笛]

select name
from s_c
where name <> 'B'
group by b
having count(1) = (select count(1) from s_c where name = 'B')

[冷箫轻笛]

--修改一下
select name
from s_c
where name <> 'B'
group by b
having count(1) >= (select count(1) from s_c where name = 'B')

[marco08]

create table s_c(Name varchar(10), Course varchar(10))
insert s_c select 'A', '语文'
union all select 'A', '数学'
union all select 'A', '英语'
union all select 'B', '语文'
union all select 'B', '英语'
union all select 'C', '数学'
union all select 'C', '英语'



select s_c.Name from s_c
inner join
(
select Course from s_c where Name='B'
)B on s_c.Course=B.Course and Name<>'B'
group by s_c.Name
having count(*)>=(select count(*) from s_c where Name='B')