gpt4 book ai didi

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

转载 作者:行者123 更新时间:2023-12-02 15:17:13 25 4
gpt4 key购买 nike

我有以下数据结构

| 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

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

最佳答案

姓名分组,只取至少有一份学生契约(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

关于sql - 如何组合 group by、where 和逻辑运算符?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/39598048/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com