SQL中Distinct值不生效：按唯一FIID统计多列数据结果异常

各位技术同仁，我绞尽脑汁仍未排查出代码问题所在。我期望按唯一的FIID（公司名称标识）统计不同列的数据：

• 预期结果：每个FIID行对应该公司的专属统计值（平均质量分、QC记录数、CMA启动记录数、各类错误数）
• 实际结果：所有FIID行显示的都是完全相同的统计数值，没有按FIID区分计算

我使用的代码如下：

IF OBJECT_ID('tempdb..#OutstandingClean') IS NOT NULL DROP TABLE #OutstandingClean
PRINT ' DROP TEMP TABLE'
SELECT * INTO #OutstandingClean FROM quality$
PRINT ' INSERT INTO TEMP TABLE'
GO
ALTER TABLE #OutstandingClean ADD [QC Date Only] NVARCHAR(255)
PRINT 'DATE COLUMN CREATED IN TEMP'
ALTER TABLE #OutstandingClean ADD [QC Time Only] NVARCHAR(255)
PRINT 'TIME COLUMN CREATED IN TEMP'
ALTER TABLE #OutstandingClean ADD [CMA Date Only] NVARCHAR(255)
PRINT 'DATE COLUMN CREATED IN TEMP'
ALTER TABLE #OutstandingClean ADD [CMA Time Only] NVARCHAR(255)
PRINT 'TIME COLUMN CREATED IN TEMP'
GO
UPDATE #OutstandingClean SET [QC Date Only] = LEFT([TSQCApproved],LEN([TSQCApproved])-7)
GO
UPDATE #OutstandingClean SET [QC Time Only] = right([TSQCApproved],8)
PRINT ' UPDATED DATE AND TIME IN TEMP TABLE'
GO
UPDATE #OutstandingClean SET [CMA Date Only] = LEFT([TSCMAStarted],LEN([TSCMAStarted])-7)
GO
UPDATE #OutstandingClean SET [CMA Time Only] = right([TSCMAStarted],8)
PRINT ' UPDATED DATE AND TIME IN TEMP TABLE'
GO
SELECT distinct FIID,
(select CONVERT(DECIMAL,(AVG(QualityScore))) from #OutstandingClean WHERE [QC Date Only] between '4/10/2018' and '4/11/2018' ) ,
(select count(distinct KYCRecordName) from #OutstandingClean where (RecordType) IN ('QC') and [QC Date Only] between '4/10/2018' and '4/11/2018' ) ,
(select count(kycrecordname) from #OutstandingClean where [TSCMAStarted] between '4/10/2018' and '4/11/2018' ) ,
(select count(ErrorType) from #OutstandingClean where [ErrorType] in ('Data Input') and [ReviewErrorCreateDate] between '4/10/2018' and '4/11/2018' ),
(select count(ErrorType) from #OutstandingClean where [ErrorType] in ('Editorial') and [ReviewErrorCreateDate] between '4/10/2018' and '4/11/2018' ),
(select count(ErrorType) from #OutstandingClean where [ErrorType] in ('Polity') and [ReviewErrorCreateDate] between '4/10/2018' and '4/11/2018' )
from #OutstandingClean
GROUP BY FIID

问题根源 #

你的子查询没有和外部查询的FIID做关联！每个子查询都是在整个临时表里统计所有符合日期条件的数据，而不是只统计当前FIID对应的行，所以所有FIID行都会显示相同的统计结果。

修正方案 #

有两种常用的解决方式，推荐第二种更高效的写法：

方式1：给子查询加上FIID关联

给每个子查询添加AND FIID = o.FIID（外部查询表别名设为o），这样每个子查询只会计算当前FIID的数据：

SELECT FIID,
(select CONVERT(DECIMAL,(AVG(QualityScore))) from #OutstandingClean WHERE [QC Date Only] between '4/10/2018' and '4/11/2018' AND FIID = o.FIID) AS AvgQualityScore,
(select count(distinct KYCRecordName) from #OutstandingClean where (RecordType) IN ('QC') and [QC Date Only] between '4/10/2018' and '4/11/2018' AND FIID = o.FIID) AS QCRecordCount,
(select count(kycrecordname) from #OutstandingClean where [TSCMAStarted] between '4/10/2018' and '4/11/2018' AND FIID = o.FIID) AS CMARecordCount,
(select count(ErrorType) from #OutstandingClean where [ErrorType] in ('Data Input') and [ReviewErrorCreateDate] between '4/10/2018' and '4/11/2018' AND FIID = o.FIID) AS DataInputErrorCount,
(select count(ErrorType) from #OutstandingClean where [ErrorType] in ('Editorial') and [ReviewErrorCreateDate] between '4/10/2018' and '4/11/2018' AND FIID = o.FIID) AS EditorialErrorCount,
(select count(ErrorType) from #OutstandingClean where [ErrorType] in ('Polity') and [ReviewErrorCreateDate] between '4/10/2018' and '4/11/2018' AND FIID = o.FIID) AS PolityErrorCount
from #OutstandingClean o
GROUP BY FIID

方式2：使用聚合函数+CASE WHEN（更高效）

这种写法只需要扫描一次表，比多个子查询的性能更好：

SELECT 
    FIID,
    CONVERT(DECIMAL, AVG(CASE WHEN [QC Date Only] BETWEEN '4/10/2018' AND '4/11/2018' THEN QualityScore END)) AS AvgQualityScore,
    COUNT(DISTINCT CASE WHEN RecordType = 'QC' AND [QC Date Only] BETWEEN '4/10/2018' AND '4/11/2018' THEN KYCRecordName END) AS QCRecordCount,
    COUNT(CASE WHEN [TSCMAStarted] BETWEEN '4/10/2018' AND '4/11/2018' THEN kycrecordname END) AS CMARecordCount,
    COUNT(CASE WHEN ErrorType = 'Data Input' AND [ReviewErrorCreateDate] BETWEEN '4/10/2018' AND '4/11/2018' THEN ErrorType END) AS DataInputErrorCount,
    COUNT(CASE WHEN ErrorType = 'Editorial' AND [ReviewErrorCreateDate] BETWEEN '4/10/2018' AND '4/11/2018' THEN ErrorType END) AS EditorialErrorCount,
    COUNT(CASE WHEN ErrorType = 'Polity' AND [ReviewErrorCreateDate] BETWEEN '4/10/2018' AND '4/11/2018' THEN ErrorType END) AS PolityErrorCount
FROM #OutstandingClean
GROUP BY FIID

另外还有个小建议：日期字段尽量用DATE或DATETIME类型存储，而不是NVARCHAR，这样日期比较更准确，也能避免格式问题。比如你拆分日期时间的部分，可以用CONVERT(DATE, TSQCApproved)来直接获取日期，比截取字符串更可靠。

内容的提问来源于stack exchange，提问作者Lalaland