如何在子查询中使用top来按列使用顺序

I've this query and it's getting error "The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified."

我有这个查询并且它得到错误“ORDER BY子句在视图，内联函数，派生表，子查询和公用表表达式中无效，除非还指定了TOP或FOR XML。”

I've solved this issue with using TOP in the inner query, but some cases I wouldn't measure the record count. Is there any other possibility to sort it out ?

我在内部查询中使用TOP解决了这个问题，但有些情况下我不会测量记录数。还有其他可能来解决它吗？

select *
from rs_column_lang
where column_id in (
    select column_id
    from rs_column
    order by column_id
)

5 个解决方案

#1

Having an order by in your inner query doesn't make sense. You're checking your outer query for matching records, but you're reading the entire table on your inner query to find the matches. Sorting that inner query serves no purpose, so SQL Server yells at you for it.

在内部查询中进行排序没有意义。您正在检查外部查询是否匹配记录，但是您正在读取内部查询的整个表以查找匹配项。对内部查询进行排序没有任何意义，因此SQL Server会对您大吼大叫。

Like the other answers indicate, assuming your goal is to sort the results, your order by should be part of your outer query, not the inner one.

与其他答案一样，假设您的目标是对结果进行排序，您的订单应该是外部查询的一部分，而不是内部查询。

The reason a top would work in the inner query is that it would change the results of the query, depending on what you're ordering by. But just changing the order would not change the results.

top在内部查询中工作的原因是它会根据您的排序方式更改查询结果。但只是改变顺序不会改变结果。

#2

Try this

尝试这个

select  * from rs_column_lang
where column_id in 
(
select column_id from rs_column
)
order by column_id

#3

select  * from rs_column_lang
where column_id in (
select column_id from rs_column
)
order by column_id

#4

column_id contains the same data in rs_column_lang and rs_column so you can simply move your ORDER BY in the outer query:

column_id包含rs_column_lang和rs_column中的相同数据，因此您只需在外部查询中移动ORDER BY：

SELECT * FROM rs_column_lang
WHERE column_id IN (
    SELECT column_id FROM rs_column
)
ORDER BY column_id

#5

You should optimize your query like this

您应该像这样优化您的查询

select A.*
from rs_column_lang A
  inner join rs_column B on A.column_id = B.column_id
order by A.column_id

because this is simple inner join you will get significant performance improvement if DB optimizer not recognized inner join

因为这是简单的内连接，如果DB优化器无法识别内部连接，您将获得显着的性能提升

#1