create table s (s# int,sname varchar (10), age int , sex varchar (2))
create table c (c# varchar (2), cname varchar (50), teacher varchar (10))
create table sc (s# int,c# varchar (2),grade int)-----将s和sc 表中的s# 的5改为3,否则没有达到要求的数据insert into s
select 1, '李强', 23, '男' union all
select 2, '刘力', 22, '女' union all
select 3, '张有', 22, '男'
insert into c
select 'k1', 'c语言', '王化' union all
select 'k5', '数据库', '成军' union all
select 'k8', '编译原理', '成军'
insert into sc
select 1, 'k1', 83 union all
select 2, 'k1', 85 union all
select 3, 'k1', 92 union all
select 2, 'k5', 90 union all
select 3, 'k5' , 84 union all
select 3, 'k8', 80select c.*
from
(select count(*) as ks from c) a,
(select s#,count(*) as ks from sc group by s#) b,
s c
where a.ks=b.ks and b.s#=c.s#--------------
s# sname age sex
----------- ---------- ----------- ----
3 张有 22 男(所影响的行数为 1 行)
create table c (c# varchar (2), cname varchar (50), teacher varchar (10))
create table sc (s# int,c# varchar (2),grade int)-----将s和sc 表中的s# 的5改为3,否则没有达到要求的数据insert into s
select 1, '李强', 23, '男' union all
select 2, '刘力', 22, '女' union all
select 3, '张有', 22, '男'
insert into c
select 'k1', 'c语言', '王化' union all
select 'k5', '数据库', '成军' union all
select 'k8', '编译原理', '成军'
insert into sc
select 1, 'k1', 83 union all
select 2, 'k1', 85 union all
select 3, 'k1', 92 union all
select 2, 'k5', 90 union all
select 3, 'k5' , 84 union all
select 3, 'k8', 80select c.*
from
(select count(*) as ks from c) a,
(select s#,count(*) as ks from sc group by s#) b,
s c
where a.ks=b.ks and b.s#=c.s#--------------
s# sname age sex
----------- ---------- ----------- ----
3 张有 22 男(所影响的行数为 1 行)
join sc on sc.s#=c.c#
join c on distinct sc.count(*)=c.count(*)
group by sc.s#
SELECT s# FROM sc GROUP BY s# HAVING(COUNT(*))=(SELECT COUNT(DISTINCT(s#)) FROM s))b
ON b.s#=s.s#
from s
where not exists (select 1
from c
where not exists (select 1
from sc
where s# = s.s#
and
c# = c.c#))