sql-server - 通过常规查询以编程方式为Microsoft SQL Server生成前n个查询

Question

我有一个生成诸如SELECT * FROM TEST ORDER BY X之类的标准SQL的程序
并将其（作为字符串）与整数一起传递给函数以创建有限的查询（也是字符串）。对于Oracle（针对其开发应用程序），此函数返回SELECT * FROM ( sq ) WHERE ROWNUM <= n，给出：

SELECT * FROM (SELECT * FROM TEST ORDER BY X) WHERE ROWNUM <= 10

哪个有效。

不幸的是，我不知道如何为Microsoft SQL Server编写等效的函数（我不是MS SQL专家）。

我试过了：

SELECT TOP 10 FROM (SELECT * FROM TEST ORDER BY X)

但这导致：

Error: Incorrect syntax near the keyword 'from'.

然后我尝试了：

SELECT TOP 10 * FROM (SELECT * FROM TEST ORDER BY X)

这让我受益：

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.

自从我指定TOP以来，这令人沮丧。

我尝试了其他几种shot弹枪变种，包括：

SELECT TOP (10) * FROM (SELECT * FROM TEST ORDER BY X)
SELECT TOP 10 * FROM (SELECT * FROM TEST ORDER BY X) SUBQUERY
SELECT TOP 10 * FROM (SELECT * FROM TEST ORDER BY X) AS SUBQUERY
SELECT * FROM (SELECT * FROM TEST ORDER BY X) WHERE ROW_NUMBER <= 10

但当然，它们都不起作用。

根本没有希望吗？

Answer 1

使用SET ROWCOUNT。这使工作非常简单。

SET ROWCOUNT 10

SELECT * FROM [TEST] ORDER BY [X]

SET ROWCOUNT 0