在 SELECT 和 ORDER BY 中，COUNT 是否执行两次？
我有一个像这样的 SQL 查询：
SET @q =
(SELECT Id AS '@Id', COUNT(Occ) AS '@Occ' FROM Details
GROUP BY Id
ORDER BY COUNT(Occ) DESC, Id ASC
FOR XML PATH('Data'), ROOT('Stats'), ELEMENTS, TYPE)
我设置 AS @Id 和 AS @Occ 以便我的 FOR XML 指令将输出转换为属性而不是元素。
我的问题： SELECT 和 ORDER BY 中两次出现 COUNT(Occ) 是否会导致计数执行两次，如果是这样，如何防止这种情况发生？
谢谢！
I have a SQL query like this:
SET @q =
(SELECT Id AS '@Id', COUNT(Occ) AS '@Occ' FROM Details
GROUP BY Id
ORDER BY COUNT(Occ) DESC, Id ASC
FOR XML PATH('Data'), ROOT('Stats'), ELEMENTS, TYPE)
I'm setting AS @Id and AS @Occ in order for my FOR XML directive to transform the output as attributes instead of elements.
My question: Does the double occurrence of COUNT(Occ) both in the SELECT and in the ORDER BY cause the count to be executed twice, and if so, how can I prevent this from happening?
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
旧版本的 SQL 要求 ORDER BY 子句中出现的任何表达式必须与 SELECT 子句中的列之一完全相同。这样表达式就不必计算两次。只要两个地方都有完全相同的表达式，就不必执行两次。
事实上，您的排序可以写为
ORDER BY 2 DESC, 1因为您可以只使用从 1 开始的列号，而不必复制表达式。无论使用哪种方式，表达式都不应该执行两次。Older versions of SQL had the requirement that any expression appearing in an
ORDER BYclause must be exactly the same as one of the columns in theSELECTclause. This was so that the expressions didn't have to be evaluated twice. As long as you have the exact same expression in both places, it won't have to be executed twice.In fact, your sort could be written as
ORDER BY 2 DESC, 1because you can just use the 1-based column number instead of having to copy the expression. Whichever way you use, the expression should not be executed twice.在“Bad Old Days”（SQL-86）中，您必须使用“
ORDER BY 2 DESC, 1 ASC”，指定您想要对数据进行排序的列号。这非常棘手，因此更高版本允许您指定要排序的相关表达式。但优化器仍然不会再次重新计算实际值。它必须知道计算值才能进行排序（并且不要忘记，在进行排序时，它可能需要多次将给定输出行的值与其他输出行的值进行比较）。In the 'Bad Old Days' (SQL-86), you would have had to use '
ORDER BY 2 DESC, 1 ASC', specifying the column numbers by which you wanted to order the data. That is pretty icky, so later versions allow you to specify the relevant expressions to sort by. But the optimizer still isn't going to recalculate the actual values a second time. It has to know the calculated value to do the sorting (and don't forget, it might need to compare the value for a given output row with the values for other output rows many times as it does the sort).通过查看执行计划，您可以准确地了解 SQL 是如何执行查询的。它可能不会计算两次，但这可能取决于优化器是否认为这样做会更有效。既然如此，我无论如何也不会去阻止。
You can see exactly how SQL executes the query by looking at the execution plan. Likely it will not count twice, but it might depending on whether the optimizer thinks that would be more efficient. In that case, I wouldn't try to stop it anyway.