27,579
社区成员
发帖
与我相关
我的任务
分享
---测试数据---
if object_id('[S]') is not null drop table [S]
go
create table [S]([S#] int,[SNAME] varchar(4),[AGE] int,[SEX] varchar(2))
insert [S]
select 1,'李强',23,'男' union all
select 2,'刘丽',22,'女' union all
select 5,'张友',22,'男'
if object_id('[C]') is not null drop table [C]
go
create table [C]([C#] varchar(2),[CNAME] varchar(10),[TEACHER] varchar(4))
insert [C]
select 'k1','C语言','王华' union all
select 'k5','数据库原理','程军' union all
select 'k8','编译原理','程军'
if object_id('[SC]') is not null drop table [SC]
go
create table [SC]([S#] int,[C#] varchar(2),[GRADE] int)
insert [SC]
select 1,'k1',83 union all
select 2,'k1',85 union all
select 5,'k1',92 union all
select 2,'k5',90 union all
select 5,'k5',84 union all
select 5,'k8',80
---查询---
select C#,Cname
from C
where not exists
(
select 1
from s
where not exists
(
select 1
from sc
where s.s#=sc.s# and c.c#=sc.c#
)
)
---结果---
C# Cname
---- ----------
k1 C语言
(所影响的行数为 1 行)
select distinct C#,CNAME From C where C# In (select C# from SC group BY C# having(count(C#)=(select count(1) from S)))