SQL Server: groupby多列生成重复结果

Question

我试图将第三列包含到我现有的SQL Server查询中，但我得到重复的结果值。

下面是tb_IssuedPermits中包含的数据示例:

| EmployeeName | Current |
|--------------|---------|
| Person A     | 0       |
| Person A     | 0       |
| Person B     | 1       |
| Person C     | 0       |
| Person B     | 0       |
| Person A     | 1       |

这是我当前的查询，基于1或0位值产生重复值。

SELECT EmployeeName, COUNT(*) AS Count, [Current]
FROM tb_IssuedPermits
GROUP BY EmployeeName, [Current]

| EmployeeName | Count | Current |
|--------------|-------|---------|
| Person A     | 2     | 0       |
| Person B     | 1     | 0       |
| Person C     | 1     | 0       |
| Person A     | 1     | 1       |
| Person B     | 1     | 1       |

关于如何修改查询以获得以下预期结果的任何想法?每个EmployeeName有一个结果行。Current为1，如果EmployeeName与Current = 1存在一行，则为0。

| EmployeeName | Count | Current |
|--------------|-------|---------|
| Person A     | 3     | 1       |
| Person B     | 2     | 1       |
| Person C     | 1     | 0       |

结果不需要以任何特定的顺序。

TIA

Answer 1

如果您的Current列包含字符串值'FALSE'和'TRUE'，您可以这样做

SELECT EmployeeName, Count(*) AS Count, 
MAX([Current]) AS Current
FROM tb_IssuedPermits
GROUP BY EmployeeName

这是一个hack，但它工作:MAX将从每个组中获得TRUE，如果有一个。

如果您的Current列是BIT，则强制转换为INT并反转换，如@ThorstenKettner所建议的。

SELECT EmployeeName,
Count(*) AS Count, 
CAST(MAX(CAST([Current] AS INT)) AS BIT) AS Current
FROM tb_IssuedPermits
GROUP BY EmployeeName

或者，您可以使用条件聚合:

SELECT EmployeeName,
Count(*) AS Count, 
CAST(COUNT(NULLIF(Current, 0)) AS BIT) AS Current
FROM tb_IssuedPermits
GROUP BY EmployeeName

Answer 2

你可以这样做

SELECT EmployeeName, Count(1) AS Count,SUM(CAST([Current]AS INT)) AS Current FROM tb_IssuedPermits GROUP BY EmployeeName