如何结合group by、where和逻辑运算符？

Question

我有以下数据结构

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

我想获取那些在其工作历史中既有学生合同又有其他合同的员工的Names。我的方法是：

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

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

- ruedi

Answer 1

按Name进行分组，只选择那些至少有一份学生合同和总合同数大于1的分组。

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

- juergen d

---

你觉得使用 SUM 来计数，然后再用 COUNT 不奇怪吗？我会在两个地方都使用 COUNT 以保持一致性，并且更清楚你想要做什么。 - Juan Carlos Oropeza

@Juan Carlos Oropeza：我不同意。使用条件聚合时，使用SUM进行计数与使用COUNT同样普遍。 - Thorsten Kettner

@JuanCarlosOropeza: 使用聚合函数count()需要知道count()不会计算NULL值，但会计算其他所有数据。 - juergen d

@JuanCarlosOropeza，这没有什么奇怪的，你只需要知道 SUM 和 COUNT 的作用。在使用函数时，我认为不需要保持一致性，因为它们用于评估不同的东西。 - Lamak

@Lamak 好吧，看起来只是我觉得有点奇怪。也许是我的强迫症作祟 :) - Juan Carlos Oropeza

Answer 2

使用 GROUP BY：

SELECT Name
FROM dbo.YourTable
GROUP BY Name
HAVING SUM(CASE WHEN [Contract Type] = 'Student' THEN 1 ELSE 0 END) >= 1
AND SUM(CASE WHEN [Contract Type] <> 'Student' THEN 1 ELSE 0 END) >= 1;

但你也可以使用 EXISTS：

SELECT DISTINCT A.Name
FROM dbo.YourTable A
WHERE EXISTS(SELECT 1 FROM dbo.YourTable
             WHERE Name = A.Name
             AND [Contract Type] = 'Student')
AND EXISTS( SELECT 1 FROM dbo.YourTable
            WHERE Name = A.Name
            AND [Contract Type] <> 'Student');

- Lamak

Answer 3

你也可以使用 GROUP BY 然后 IN：

SELECT name
FROM table
GROUP BY name
HAVING COUNT(*) > 1
AND name IN
(SELECT name
 FROM table
 WHERE contracttype = 'student')

Tested here: http://sqlfiddle.com/#!9/38fa36/1

- kjmerf

---

这样做不起作用，因为 and name in () 部分不在 where 子句中，而是在 having 子句中。 - juergen d

没问题，它仍然可以工作。WHERE 子句在子查询中，所以我们很好。请看这里：http://sqlfiddle.com/#!9/38fa36/1 - kjmerf

OP正在使用SQL Server而不是MySQL。而MySQL的这个“特性”会带来更多混乱，而不是有用。 - juergen d

@juergend 这在 SQL Server 中运行良好，毫无问题。 - Lamak

@juergend 说实话，我也没想到会这样，所以我试了一下，结果很惊讶它居然可以运行。 - Lamak

这一点并不奇怪。name 在 group by 子句中，所以当然可以在 having 子句中使用它。 - Thorsten Kettner