英文:
Select Distinct has Duplicates
问题
我有这个查询，明显会产生重复项，但我不明白为什么，因为我在这里使用了 DISTINCT 选项。
我刚刚从运行 SQL 版本 12.0.6329.1 的一个 SQL 服务器迁移到了 13.0.6419.1（我相信是从 2014 到 2016）
但在旧服务器上，我没有遇到相同的问题。
你有什么想法，为什么 DISTINCT 没有按我期望的方式工作？
SELECT DISTINCT
[UWI_vn]
,[WI_PrdWellCnt]
,[AAV_GUID]
,[InResFlag]
FROM [AAV_WellStore].[dbo].[V_ResultsProdBdgtOpsUpLiveBaseV4.5]
WHERE [InResFlag] =1
AND [WI_PrdWellCnt] > 0
AND [UWI_vn] = '102/16-25-069-05W6/0'
英文:
I have this query that is clearly producing duplicates, but I don't see why since I have the DISTINCT option in use here.
I just migrated SQL servers from one running SQL version 12.0.6329.1 to 13.0.6419.1 (2014 to 2016 I believe)
and I don't experience the same issue on the old server.
Any ideas why DISTINCT isn't working as [I] expected?
SELECT DISTINCT
[UWI_vn]
,[WI_PrdWellCnt]
,[AAV_GUID]
,[InResFlag]
FROM [AAV_WellStore].[dbo].[V_ResultsProdBdgtOpsUpLiveBaseV4.5]
WHERE [InResFlag] =1
AND [WI_PrdWellCnt] > 0
AND [UWI_vn] = '102/16-25-069-05W6/0'
答案1
得分: 1
感谢 dfundako 提供的校验和技巧，以及 ThorstenKettner 和 KeithL 提出的浮点列的固有属性。
[WI_PrdWellCnt] 列是一个浮点数，并通过一个 CTE 进行聚合，将数百行减少到一行。这个平均值可能是引起问题的原因。如果所有值相同，你会期望平均值是相同的，但实际上并非如此。我们将这个值分离出来并单独计算，这样就不必处理这个问题了。
将 [WI_PrdWellCnt] 转换为实数也可能解决这个问题。 （在这个主题中建议转换为十进制会失去精度，在我的示例中四舍五入为1）这些表是由专有应用程序生成的，因此修改基础表不是一个选择。
英文:
Thanks to dfundako for the checksum trick and ThorstenKettner and KeithL for bringing up the intrinsic properties of the float column.
The [WI_PrdWellCnt] column is a float and goes through a CTE that aggregates hundereds of rows to get down to one row. This average must be whats causing the issue. You'd expect the average to be the same if all values are the same, but they arent We've broken this value out and calculated it separately so we don't have to deal with this issue.
Casting [WI_PrdWellCnt] to Real would also potentially solve the issue. (Casting to Decimal as suggested in this thread loses precision and rounds up to 1 in my example) The tables are produced by a proprietary application so altering the base tables is not an option.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论