sql-server - 使用大小写时的不同计数

Question

在 SQL Server 中使用 case when 和 distinct 时，我无法获得正确的不同计数。

我有一列用于计算正确计数的 count(distinct TA_STUDENT_ID)。然后我有 2 个额外的列，我在其中按 TA_LOCATION 进行计数，并且 TA_LOCATION 下每行上的数字的总数需要加起来为 DISTINCT TA_STUDENT_ID 的 COUNT。我需要帮助才能做到这一点。这是我的查询：

SELECT 
count  (distinct TA_STUDENT_ID) as 'count',
COUNT (CASE WHEN (TA_LOCATION = 'CCC') THEN 'CCC'  END) AS 'CCC',
COUNT(CASE WHEN (TA_LOCATION = 'SCCDC') THEN 'SCCDC'  END) AS 'SCCDC',      
[TA_AW_ID]
FROM  [S85_TA_ACYR]
group by   [TA_AW_ID]
order by  [TA_AW_ID]

我想要的结果是 TA_LOCATION 列中每一行的总数应该总计并与我的 COUNT DISTINCT TA_STUDENT_ID 列中的行上的数字相匹配。

标签： sql-serverdistinctcase-when

Answer 1

使用DISTINCTinside COUNT()withCASE表达式：

SELECT COUNT(DISTINCT TA_STUDENT_ID) as 'count',
       COUNT(DISTINCT CASE WHEN (TA_LOCATION = 'CCC') THEN TA_STUDENT_ID  END) AS 'CCC',
       COUNT(DISTINCT CASE WHEN (TA_LOCATION = 'SCCDC') THEN TA_STUDENT_ID  END) AS 'SCCDC',      
       [TA_AW_ID]
FROM  [S85_TA_ACYR]
GROUP BY [TA_AW_ID]
ORDER BY [TA_AW_ID];