查询只选修了1号课程和3号课程的学生学号

[wyf1313]

这是sc的表

试了几种方法但是想不通其中一种也就是下面代码，明明有一个统计cno的个数为2的条件限制为什么最后还会出现95001的学号，95001明明选了3节课
use School
select Sno
from SC
where Cno in ('1','3')
group by Sno
having COUNT(Cno)=2

这是结果

[唐诗三百首]

select Sno
 from SC a
 where Cno in('1','3')
 and not exists(select 1 from SC b where b.Sno=a.Sno and b.Cno not in('1','3'))
 group by Sno
 having count(1)=2

[qq_37753824]

Create table #SC ( Sno varchar(255), Cno varchar(255), Grade decimal(4,1) ) insert into #SC select '95001','1',92 union all select '95001','2',85 union all select '95001','3',88 union all select '95002','1',90 union all select '95003','1',82 union all select '95003','3',85 select Sno from #SC where exists(select 1 from #SC a where a.Sno=#SC.Sno and a.Cno='3') and not exists(select 1 from #SC b where b.Sno=#SC.Sno and b.Cno not in('1','3')) and Cno='1' drop table #SC

[飞虹147]

你用了where ,第一步就把非 1，3 的选课记录给过滤了，再做group by， 当然 95001 count(*) 的值为 2 ，满足条件。 have a try like this: select sno from #SC group by sno having sum(case when cno in (1,3) then 1 else 2 end) = 2

[qq_30412191]

最后还会出现95001的学号，95001明明选了3节课 ，因为 Cno in ('1','3') 把2的记录过滤掉了，不就剩下2节课了吗

[jereco]

Create table #SC
(
Sno varchar(255),
Cno varchar(255),
Grade decimal(4,1)
)
insert into #SC
select '95001','1',92
union all
select '95001','2',85
union all
select '95001','3',88
union all
select '95002','1',90
union all
select '95003','1',82
union all
select '95003','3',85

select Sno
from #SC a
where  exists (select Sno,count(cno) from #SC b where a.Sno = b.sno  group by sno having count(cno)=2 )
group by Sno

[风吹陀螺]

having COUNT(Cno)=2的意思，是Cno重复出现的次数为2；而不是同一人所修课程个数。
读图可知1出现了3次，2出现了1次，3出现了2次。
即操作对象为
-----
95001 3 88
95003 3 85
-----
如何获取所修课程个数为2，其他楼提供了很多思路了。

[yingbo833]

select Sno from SC where Cno=1 or Cno=3 group by Sno having count(Sno)>1

[weixin_43891821]

@：recover 这种方法如果选修的课程变成了1,2,3或者1,2,3,4是不是不好扩展?

[：recover]

select sno from sc where cno='1' and sno in(select sno from sc where cno='3');