sql - 如何组合 group by、where 和逻辑运算符？

Question

我有以下数据结构

| Name  | Contract Type |
|:------|--------------:|
| Frank | Student       |
| Frank | Staff         |
| Frank | Staff         |
| John  | Internship    |
| John  | Student       |
| John  | Staff         |
| Tim   | Internship    |
| Tim   | Staff         |

我想获取拥有学生契约(Contract)和历史上另一份契约(Contract)的员工的姓名。我的方法是

Select Name, count(Name) from table
where ContractType = ('Student') AND (ContractType = ('Staff') OR ContractType = ('Internship') )     
group by Name having count (Name) > 1

这里的问题是我仍然得到所有员工。谁能帮我完成这个任务？

Answer 1

按姓名分组，只取至少有一份学生契约(Contract)且契约(Contract)总数超过一份的那些组

Select Name
from your_table
group by Name 
having sum(case when ContractType = 'Student' then 1 else 0 end) > 0
   and count(distinct ContractType) > 1