确定记录出现的次数

I have two tables - student and interview - with values:

我有两个表格——学生和面试——有价值观:

student
----------
sID  
sName  

interview
----------
sID  
date  
outcome = (OFFER, NOOFFER, HIRED)

And I have to list the sID of any student who has never received an offer but who has had more than five interviews.

我必须列出所有从未收到录取通知书但参加过五次以上面试的学生的名单。

I am having trouble figuring out how to determine if a student has had more than 5 interviews. I know you can just count the records to see if the sID is listed more than 5 times, but I am not exactly sure how to format it. Any help would be greatly appreciated!

我不知道如何确定一个学生是否参加过5次以上的面试。我知道您可以数一下记录，看看sID是否被列出超过5次，但我不确定如何格式化它。如有任何帮助，我们将不胜感激!

2 个解决方案

#1

Use:

使用:

SELECT s.sid
  FROM STUDENT s
 WHERE EXISTS(SELECT NULL -- more than five interviews
                FROM INTERVIEW i 
               WHERE i.sid = s.sid
            GROUP BY i.sid
              HAVING COUNT(*) > 5)
   AND NOT EXISTS(SELECT NULL -- never received an offer
                    FROM INTERVIEW i
                   WHERE i.sid = s.sid
                     AND i.outcome = 'OFFER')

2 个解决方案

#1

更多相关文章

随机推荐