为什么索引视图不能有 MAX() 聚合？
我一直在尝试一些索引视图，印象深刻，但我几乎总是需要最大值或最小值，并且无法理解为什么它不适用于这些视图，任何人都可以解释为什么吗？
我知道他们是不允许的，我只是不明白为什么！允许计数等，为什么不允许 MIN/MAX，我正在寻找解释......
I have been trying out a few index views and am impressed but I nearly always need a max or a min as well and can not understand why it doesn't work with these, can anyone explain why?
I KNOW they are not allowed, I just can't understand why!!! Count etc. is allowed why not MIN/MAX, I'm looking for explanation...
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
不允许使用这些聚合，因为它们不能仅根据更改的值重新计算。
某些聚合（例如
COUNT_BIG()或SUM()）只需查看更改的数据即可重新计算。这些在索引视图中是允许的，因为如果基础值发生变化，可以直接计算该变化的影响。其他聚合（例如
MIN()和MAX()）不能仅通过查看正在更改的数据来重新计算。如果删除当前的最大值或最小值，则必须在整个表中搜索并找到新的最大值或最小值。同样的原则也适用于其他聚合，例如
AVG()或标准变体聚合。 SQL 无法仅根据更改的值重新计算它们，而是需要重新扫描整个表以获取新值。These aggregates are not allowed because they cannot be recomputed solely based on the changed values.
Some aggregates, like
COUNT_BIG()orSUM(), can be recomputed just by looking at the data that changed. These are allowed within an indexed view because, if an underlying value changes, the impact of that change can be directly calculated.Other aggregates, like
MIN()andMAX(), cannot be recomputed just by looking at the data that is being changed. If you delete the value that is currently the max or min, then the new max or min has to be searched for and found in the entire table.The same principle applies to other aggregates, like
AVG()or the standard variation aggregates. SQL cannot recompute them just from the values changed, but needs to re-scan the entire table to get the new value.索引视图不支持 MIN/MAX 等聚合函数。您必须在围绕视图的查询中执行 MIN/MAX 操作。
关于索引视图中允许和不允许的内容有完整的定义 此处 (SQL 2005)。
报价：
Aggregate functions like MIN/MAX aren't supported in indexed views. You have to do the MIN/MAX in the query surrounding the view.
There's a full definition on what is and isn't allowed within an indexed view here (SQL 2005).
Quote:
如果您只想在使用视图时查看排序的内容而不添加排序依据，我只需在其中添加一列并排序。
if you just want to see things ordered without adding a sort by when you use a view I just add a column with and order by in it.
除了 Remus 指定的原因外，支持 MIN 和 MAX 的实际需要较少。
与 COUNT() 或 SUM() 不同，MAX 和 MIN 的计算速度很快 - 只需一次查找即可完成设置 - 无需读取大量数据。
Besides the reasons specified by Remus, there is less practical need to support MIN and MAX.
Unlike COUNT() or SUM(), MAX and MIN are fast to calculate - you are all set after just one lookup - you don't need to read a lot of data.