SQL 如何按计算字段分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15900999/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to group by a Calculated Field
提问by Jonny
I need to group by a Calculated field ins SQL Server 2005/2008.
我需要按 SQL Server 2005/2008 中的计算字段进行分组。
I have the following sql:
我有以下 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.DateDueI need to group by the result of the calculation. Is it possible ?
而不是group by mwspp.DateDue我需要按计算结果分组。是否可以 ？
Thanks in advance
提前致谢
回答by Ian Preston
Sure, just add the same calculation to the GROUP BYclause:
当然，只需在GROUP BY子句中添加相同的计算：
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 Scalaroperator.
就像所有关于优化器的问题一样，答案实际上是“视情况而定”，但很可能它只会执行一次 - 您会在执行计划中将其视为计算标量运算符。
Based on this Compute Scalaroperation, 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.
这里的其他答案（CTE/子查询等）都同样有效，但并没有真正改变逻辑——最终它们将执行类似的操作。SQL Server 可能会以不同的方式对待它们，但这不太可能。但是，它们将有助于提高可读性。
If you're worried about efficiency you can look at a couple of options, e.g. setting up the calculation as a persisted computed columnand 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.
真正确定的唯一方法是在对典型数据集运行查询时检查执行计划/IO 统计信息，并查看您是否对性能感到满意；如果没有，也许调查上述选项之一。
回答by Taryn
If you want to GROUP BYthe dateadd calculation, then you will either need to place that formula in the GROUP BYclause or you can wrap your query in another SELECT:
如果要进行GROUP BYdateadd 计算，则需要将该公式放在GROUP BY子句中，或者可以将查询包装在另一个 SELECT 中：
select DateCalc,
sum(QtyRequired) TotalQty
from
(
select mwspp.DateDue,
dateadd(day, -7, Convert(DateTime, mwspp.DateDue) + (7 - datepart(weekday, mwspp.DateDue))) DateCalc,
mwspp.QtyRequired
from manufacturingweekshortagepartpurchasing mwspp
where mwspp.buildScheduleSimID = 10109
and mwspp.partID = 8366
) src
group by DateCalc
order by DateCalc
回答by Taryn
There are several ways of doing so - one involves using a CTE:
有几种方法可以这样做 - 一种涉及使用 CTE：
with cte as
(select m.*,
dateadd(day, -7, Convert(DateTime, DateDue) + (7 - datepart(weekday, DateDue))) datecalc
from manufacturingweekshortagepartpurchasing m)
select datecalc, sum(QtyRequired)
from cte
where buildScheduleSimID = 10109 and partID = 8366
group by datecalc
order by datecalc
回答by Praveen Nambiar
Simply place the calculation in the GROUP BYclause:
只需将计算放在GROUP BY子句中：
Replace
代替
group by mwspp.DateDue
To
到
group by dateadd(day, -7, Convert(DateTime, mwspp.DateDue) + (7 - datepart(weekday, mwspp.DateDue)))
