奇怪的秩序与团结一致的行为

Can someone explain to me what is happening here. The behavior is not as I expect:

有人能告诉我这里发生了什么吗?这种行为并不像我预期的那样:

select category,timefield from test where category='A'
union
select top 10 category,timefield from test where category='B' order by timefield desc

What I want is all rows of category A and only the 10 most recent from category B.
When executed, the results are actually: All of category A (sorted by timefield descending) + the first 10 rows of category B (sorted by timefield descending)

我想要的是A类的所有行，以及B类中最近的10行。执行时，结果实际上是:A类的所有行(按时间字段降序排序)+ B类的前10行(按时间字段降序排序)

Stranger still, if we take this whole statement and place it in a subquery, I would expect the behavior to remain the same and evaluated as before, but it is not:

更奇怪的是，如果我们把这整个语句放到子查询中，我希望行为保持不变并与以前一样进行评估，但它不是:

select * from (
    select category,timefield from test where category='A'
    union
    select top 10 category,timefield from test where category='B' order by timefield desc
) subq

This returns all of category A (in ascending order) + the 10 most recent of category B (in descending order). Which is exactly what I want.

这将返回所有类别A(按升序)+最近的10个类别B(按降序)。这正是我想要的。

Why does the first statement not produce what I expect and why does the second behave very differently ??

为什么第一个语句不能产生我所期望的结果，为什么第二个语句的行为非常不同?

(This is SQL Server 2008)

(这是SQL Server 2008)

1 个解决方案

#1

You need parentheses in the first query, because the order by is applied to the result of the union. It is interpreted as:

在第一个查询中需要圆括号，因为该命令应用于union的结果。这是解释为:

(select category,timefield from test where category='A'
 union
 select top 10 category,timefield from test where category='B'
)
order by timefield desc

(I'm not saying this is valid syntax.)

(我不是说这是有效的语法。)

whereas what you want is:

而你想要的是:

(select category, timefield from test where category='A')
union
(select top 10 category, timefield from test where category='B' order by timefield desc)

Do note that union will remove duplicates. If you don't want this additional overhead, use union all instead.

请注意union将删除副本。如果您不想要这个额外的开销，那么请使用union all。

As to why this works as a subquery, my guess is that it is a coincidence. SQL Server is not going to guarantee the results being returned when you use top without an order by -- or even that the results are consistent from one call to the next. Sometimes it might actually do what you want.

至于为什么它作为子查询有效，我的猜测是这是一个巧合。SQL Server不会保证在使用top时不需要order by时返回结果，甚至不会保证从一个调用到下一个调用的结果是一致的。有时候，它可能真的会做你想做的事。

#1