sql选择和某同学选修课完全相同的学生，多种方法

sc表

期待的运行结果：

题一

选择和学号为1的同学选修课完全相同的学生

法一：我不在那些选修了 他没有选的课 的人中，他不在那些选修了 我没有选的课 的人中

最优雅的一种方法

select distinct sno
from sc x
where
--我不在那些选修了 他没有选的课 的人中，即他选的课包含我选的课
 x.sno not in 
	(select sno from sc where cno not in
		(select cno from sc where sno='1'))
and 
--他不在那些选修了 我没有选的课 的人中，即我选的课包含他选的课
'1' not in  
	(select sno from sc where cno not in
		(select cno from sc where sno=x.sno))

法二：不存这么一门课，他没选我选了 并且 不存这么一门课，他选了我没选

多次not exists

select distinct sno
from sc x
where not exists(
    select * from sc y where 
		not exists
			(select * from sc q where q.sno='1' and q.cno=y.cno)
        and 
        x.sno=y.sno
)--不存这么一门课，他没选我选了
and  not exists(
    select * from sc z where 
  		z.sno='1'
        and  
        not exists
        	(select * from sc p where x.sno=p.sno and z.cno=p.cno )
 )--不存这么一门课，他选了我没选

法三：

法二复杂版，但更好理解

select distinct sno
from sc x
where not exists(
    select * from sc y where 
		not exists --在SC表中不存在这么一行：学号是1，课号是y的课号
			(select * from sc q where q.sno='1' and q.cno=y.cno)
        and 
        exists     --在SC表中存在这么一行：学号是我，课号是y的课号
        	(select * from sc r where x.sno=r.sno and y.cno=r.cno )
)--不存这么一门课，他没选我选了
and  not exists(
    select * from sc z where 
  		exists
			(select * from sc s where s.sno='1' and s.cno=z.cno)
        and  
        not exists
        	(select * from sc p where x.sno=p.sno and z.cno=p.cno )
 )--不存这么一门课，他选了我没选

这个方法还可以这样写，涉及S、C、SC三个表，看起来似乎更严谨：

select distinct sno
from s x
where not exists(
    select * from c y where 
		not exists --在SC表中不存在这么一行：学号是1，课号是y的课号
			(select * from sc q where q.sno='1' and q.cno=y.cno)
        and 
        exists     --在SC表中存在这么一行：学号是我，课号是y的课号
        	(select * from sc r where x.sno=r.sno and y.cno=r.cno )
)--不存这么一门课，他没选我选了
and  not exists(
    select * from c z where 
  		exists
			(select * from sc s where s.sno='1' and s.cno=z.cno)
        and  
        not exists
        	(select * from sc p where x.sno=p.sno and z.cno=p.cno )
 )--不存这么一门课，他选了我没选

法四：

法一法二结合版

select distinct sno
from sc x
where not exists(
    select * from sc y where 
		y.cno not in(select cno from sc where sno='1')
        and 
		x.sno=y.sno
)--不存这么一门课，他没选我选了
and  not exists(
       select * from sc z where 
         z.sno='1'
         and  
		 z.cno not in (select p.cno from sc p where x.sno=p.sno )
 )--不存这么一门课，他选了我没选

法五：

和法四类似

select distinct sno
from sc x
where not exists(
    select * from sc y where 
		'1' not in (select sno  from sc p where p.cno= y.cno )
        and 
		x.sno=y.sno
)--不存这么一门课，他没选我选了
and  not exists(
       select * from sc z where 
         z.sno='1'
         and  
		 x.sno not in (select sno  from sc p where p.cno= z.cno )
 )--不存这么一门课，他选了我没选

法六：

集合运算

select distinct sno
from sc x
where --我选的课包含他选的课
'1' not in  
	(select sno from sc where cno not in
		(select cno from sc where sno=x.sno))
except --删掉多选的人，即删掉选了 他没有选的课 的人
select sno from sc where cno not in
	(select cno from sc where sno='1')

或

select distinct sno
from sc x
where --他选的课包含我选的课
x.sno not in 
	(select sno from sc where cno not in
		(select cno from sc where sno='1'))
except --删掉少选的人
select distinct sno
from sc x
where '1' in  
	(select sno from sc where cno not in
		(select cno from sc where sno=x.sno))

或

select distinct sno
from sc x
where --他选的课包含我选的课
x.sno not in 
	(select sno from sc where cno not in
		(select cno from sc where sno='1'))
except 
select distinct sno
from sc x
where exists(
    select * from sc z where 
  		z.sno='1'
        and  
        not exists
        	(select * from sc p where x.sno=p.sno and z.cno=p.cno )
)

或

select distinct sno
from sc x
where
 x.sno not in 
	(select sno from sc where cno not in
		(select cno from sc where sno='1'))

intersect

select distinct sno
from sc x
where 
'1' not in  
	(select sno from sc where cno not in
		(select cno from sc where sno=x.sno))

有些方法还需要先分组再count比较数目，太不优雅，我就不写了。

题二

选择选修课程 为且仅为 1和2的学生

法一：

对应题一中的法一

select distinct sno  from SC x
where
x.Sno not in (select Sno from SC where cno not in('1','2'))
and 
'1' in (select cno from sc y where x.sno=y.sno)
and
'2' in (select cno from sc z where x.sno=z.sno)

法二：

对应题一法四和法五

select distinct sno
from sc x
where not exists(
    select * from sc y where 
		y.cno not in ('1','2')
        and 
        x.sno=y.sno
)
and  not exists(
    select * from sc z where 
  		z.cno in ('1','2')
        and  
        x.sno not in (select sno  from sc p where p.cno= z.cno )
		--或z.cno not in (select p.cno from sc p where x.sno=p.sno )
 )

法三：

对应题一法三，可涉及S、C、SC三个表

select distinct sno
from s x
where not exists(
    select * from c y where 
		y.cno not in ('1','2')
        and 
        exists     
        	(select * from sc r where x.sno=r.sno and y.cno=r.cno )
)
and  not exists(
    select * from c z where 
  		z.cno in ('1','2')
        and  
        not exists
        	(select * from sc p where x.sno=p.sno and z.cno=p.cno )
 )

法四：

集合运算

select sno from sc where cno='1'
intersect
select sno from sc where cno='2'
except 
select sno from sc where cno not in('1','2')

法五：

法一法四结合

select distinct sno  from SC x
where
x.Sno not in (select Sno from SC where cno not in('1','2'))
intersect
select sno from sc where cno='1'
intersect
select sno from sc where cno='2'