如何按计算字段分组 [英] How to group by a Calculated Field

问题描述

我需要在 SQL Server 2005/2008 中按计算字段分组.

I need to group by a Calculated field ins SQL Server 2005/2008.

我有以下sql:

select dateadd(day, -7, Convert(DateTime, mwspp.DateDue) + (7 - datepart(weekday, mwspp.DateDue))),
sum(mwspp.QtyRequired)
from manufacturingweekshortagepartpurchasing mwspp
where mwspp.buildScheduleSimID = 10109 and mwspp.partID = 8366
group by mwspp.DateDue
order by mwspp.DateDue

不是 group by mwspp.DateDue 我需要按计算结果分组.可能吗?

Instead of group by mwspp.DateDue I need to group by the result of the calculation. Is it possible ?

提前致谢

推荐答案

当然，只需在 GROUP BY 子句中添加相同的计算:

Sure, just add the same calculation to the GROUP BY clause:

select dateadd(day, -7, Convert(DateTime, mwspp.DateDue) + (7 - datepart(weekday, mwspp.DateDue))),
sum(mwspp.QtyRequired)
from manufacturingweekshortagepartpurchasing mwspp
where mwspp.buildScheduleSimID = 10109 and mwspp.partID = 8366
group by dateadd(day, -7, Convert(DateTime, mwspp.DateDue) + (7 - datepart(weekday, mwspp.DateDue)))
order by dateadd(day, -7, Convert(DateTime, mwspp.DateDue) + (7 - datepart(weekday, mwspp.DateDue)))

评论后

就像所有关于优化器的问题一样，答案实际上是视情况而定"，但很可能只会执行一次 - 您会在执行计划中看到它作为 Compute Scalar 运算符.

Like all questions regarding the optimiser, the answer is really "it depends", but most likely it will only be performed once - you'll see this in the execution plan as a Compute Scalar operator.

基于这个计算标量操作，优化器将决定如何执行实际的聚合.

Based on this Compute Scalar operation, the optimiser will then decide how to perform the actual aggregation.

这里的其他答案(CTE/子查询等)都同样有效，但并没有真正改变逻辑 - 最终它们将执行类似的操作.SQL Server 可能会以不同的方式对待它们，但这不太可能.但是，它们将有助于提高可读性.

The other answers here (CTE/subquery, etc) are all equally valid, but don't really change the logic - ultimately they will be performing similar operations. SQL Server might treat them differently but it's unlikely. However they will help with readability.

如果您担心效率，可以查看几个选项，例如将计算设置为持久计算列并使用它在索引中，或将临时结果添加到临时表中.

If you're worried about efficiency you can look at a couple of options, e.g. setting up the calculation as a persisted computed column and using this in an index, or adding interim results into a temporary table.

真正确定的唯一方法是在对典型数据集运行查询时检查执行计划/IO 统计信息，并查看您是否对性能感到满意；如果没有，也许可以调查上述选项之一.

The only way to really know for sure is to inspect the Execution Plan/IO statistics when running the query on a typical data set and seeing if you're satisfied with the performance; if not, perhaps investigating one of the above options.

这篇关于如何按计算字段分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！