如何按计算字段分组
我需要在 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.
相关文章