索引视图和T-sql,它们与SQL Server 2008 R2中的DISTINCT具有相同的结果
我想用这样的t-sql创建索引视图MyView
Select
o.Theme_ID as Theme_ID,
DATEADD(day, DATEDIFF(day, 0, o.Object_CreationDate), 0) as Objext_CreationDate,
Count_BIG(*) as ObjectCount, o.Object_Tonality from [dbo].Object o
inner join [dbo].Theme t on o.Theme_ID = t.Theme_ID
inner join [dbo].[ThemeWorkplace] tw on t.Theme_ID = tw.Theme_ID
inner join [dbo].Workplace w on w.Workplace_ID = tw.Workplace_ID
... where t.Theme_DeletedMark = 0
AND (w.Workplace_AccessType = 1 OR w.Workplace_AccessType = 8)
AND Object_DeletedMark = 0 ...
Group BY o.Theme_ID,o.Object_Tonality, DATEADD(day, DATEDIFF(day, 0, o.Object_CreationDate), 0)
这个t-sql工作正常,允许在MyView上设置聚簇索引。
问题是表ThemeWorkplace
包含几个具有相同Theme_ID
的记录。
甚至我使用GROUP BY
- 我得到Object_Count
值
等于:(真实Object_Count
值)*计数(Theme_ID
inThemeWorkplace
)。
我不能在t-sql中使用DISTINCT
字,因为在这种情况下,无法在视图上创建索引。
在我看来,有什么建议可以得到正确的结果?
没有找到相关结果
已邀请:
1 个回复
荤碗