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

问题描述

我有以下sql：

  select dateadd（day，-7，Convert（DateTime，mwspp.DateDue）+（7  -  datepart（weekday，mwspp.DateDue））），
 sum（ mwspp.QtyRequired）
 from manufacturingweekshortagepartpurchasing mwspp 
其中mwspp.buildScheduleSimID = 10109和mwspp.partID = 8366 
 group by mwspp.DateDue 
 order by mwspp.DateDue 
  

而不是 group by mwspp.DateDue 我需要由计算结果。是否有可能？

预先感谢

解决方案

将相同的计算添加到 GROUP BY 子句中：

  select dateadd（ -7，Convert（DateTime，mwspp.DateDue）+（7  -  datepart（weekday，mwspp.DateDue））），
 sum（mwspp.QtyRequired）
 from manufacturingweekshortagepartpurchasing mwspp 
 where mwspp.buildScheduleSimID = 10109和mwspp.partID = 8366 
由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）））
  

在评论后编辑：

像所有关于优化器的问题一样，答案是 ，但最有可能只会执行一次 - 您将在执行计划中将其视为计算标量运算符。

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

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

如果您担心效率问题，可以查看几个选项，例如将计算设置为持久计算的列并使用此在临时表中添加临时结果。

真正知道的唯一方法是在运行查询时检查执行计划/ IO统计信息一个典型的数据集，看看你是否满意性能;如果没有，也许正在调查上述选项之一。

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

I have the following 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

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

Thanks in advance

解决方案

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)))

Edit after comment:

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.

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.

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屋！