我有以下查询来检索大量数据,但不知怎么它为某些记录创建了重复的行。
我在这里做错了什么
SELECT Distinct dbo.tblAssessmentEcosystemCredit.ManagementZoneID, AssessmentEcosystemCreditID,dbo.tblAssessmentEcosystemCredit.AssessmentVersionID ,
(COALESCE(dbo.tblManagementZone.SiteValueCurrentScore,0)
-COALESCE(dbo.tblManagementZone.SiteValueFutureScore,0)) AS LossinSiteValueScore,
5 AS SaveType, dbo.ufn_varbintohexstr(dbo.tblAssessmentEcosystemCredit.RowTimestamp) AS RowTimestamp,
dbo.tblVegetationZone.EECID,
CASE WHEN dbo.tblVegetationZone.EECID > 0 THEN 3.0
ELSE 1.0
END AS EECOffSetMultiplier
FROM dbo.tblAssessmentEcosystemCredit
INNER JOIN dbo.tblVegetationType
ON dbo.tblAssessmentEcosystemCredit.VegTypeID = dbo.tblVegetationType.VegTypeID
INNER JOIN dbo.tblManagementZone
ON dbo.tblAssessmentEcosystemCredit.ManagementZoneID = dbo.tblManagementZone.ManagementZoneID
INNER JOIN dbo.tblVegetationZone
ON dbo.tblVegetationZone.VegetationZoneID = dbo.tblManagementZone.VegetationZoneID
INNER JOIN dbo.tblAssessmentVersion AV ON
AV.AssessmentVersionID = dbo.tblAssessmentEcosystemCredit.AssessmentVersionID
INNER JOIN tblAssessment TBA ON
TBA.AssessmentID = AV.AssessmentID
WHERE dbo.tblAssessmentEcosystemCredit.AssessmentVersionID= @AssessmentVersionID发布于 2015-04-23 01:36:11
可能您的中央基表dbo.tblAssessmentEcosystemCredit中有重复行。这应该很容易检查,因为您知道要查看哪些行。
更有可能的是,您将获得与几个dbo.tblAssessmentEcosystemCredit行对应的多个结果行,因为您要加入的其中一个表对这些行具有多个匹配项。也就是说,这些列中有一个包含至少一个重复的值:
dbo.tblVegetationType.VegTypeID
dbo.tblManagementZone.ManagementZoneID
dbo.tblVegetationZone.VegetationZoneID
dbo.tblAssessmentVersion.AssessmentVersionID
tblAssessment.AssessmentID负责任的列不能受单列UNIQUE约束的约束,也不能是其表的单列主键,这样可以帮助缩小它的范围。注意,整个行不需要重复,只需要ID。
https://stackoverflow.com/questions/29811628
复制相似问题