什么是MySQL语法LIMIT x，y的T-SQL等价物？ [重复]

Possible Duplicate:
How to write a (MySQL) “LIMIT” in SQL Server?

可能重复：如何在SQL Server中编写（MySQL）“LIMIT”？

How can I change my query with LIMIT Inside for a SQL-Server ?

如何使用LIMIT Inside为SQL-Server更改我的查询？

Code:

码：

SELECT apretiz FROM tableApoint WHERE price = '$newprice' LIMIT 5;

Many things are not working so just asking for help

许多事情都没有起作用，所以只是寻求帮助

And how can i change LIMIT 5,10 by example ? Can't I use TOP for it ?

我怎么能通过例子改变LIMIT 5,10？我不能用TOP吗？

4 个解决方案

#1

As i said it less than one hour ago, you have to use TOP ! (LIMIT is used for MYSQL)

正如我在不到一小时前说的那样，你必须使用TOP！ （LIMIT用于MYSQL）

So try to remove LIMIT 5 and do SELECT TOP(5) apretiz.

因此，尝试删除LIMIT 5并执行SELECT TOP（5）apretiz。

Also, try to add order by (same reason than before).

此外，尝试添加顺序（与以前相同的原因）。

Please make a search before asking things. Link to old question

请在询问之前进行搜索。链接到旧问题

#2

As of SQL Server 2012, you can write

从SQL Server 2012开始，您可以编写

...
ORDER BY thisColumn, thatColumn
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY

#3

Use the TOP keyword:

使用TOP关键字：

 SELECT TOP 5 pretiz 
 FROM tableApoint WHERE price = '$newprice'

Using LIMIT 5, 10 is not part of the SQL Standard and is only available in MySQL.

使用LIMIT 5,10不是SQL标准的一部分，仅在MySQL中可用。

You could use ROW_NUMBER() for SQL as a temp solution and will get you the same desired output.

您可以将SQL的ROW_NUMBER（）用作临时解决方案，并获得相同的所需输出。

SELECT * FROM ( 
  SELECT *, ROW_NUMBER() OVER (ORDER BY name) as row FROM tableApoint 
) a WHERE row >= 5 and row <= 10

#4

SQL Server 2005 and above

If you are using SQL Server 2005 and above, you could use ROW_NUMBER function to assign unique numbers to your rows and then pick the range of values from the output.

如果您使用的是SQL Server 2005及更高版本，则可以使用ROW_NUMBER函数为行指定唯一编号，然后从输出中选择值范围。

Script:

脚本：

CREATE TABLE table1
(
    textvalue VARCHAR(10) NOT NULL
);

INSERT INTO table1 (textvalue) VALUES
   ('i'),
   ('a'),
   ('e'),
   ('h'),
   ('c'),
   ('l'),
   ('g'),
   ('m'),
   ('d'),
   ('k'),
   ('j'),
   ('f'),
   ('b'),
   ('n');

;WITH letters as
(
    SELECT  textvalue
        ,   ROW_NUMBER() OVER(ORDER BY textvalue) rownum 
    FROM    table1
)
SELECT  textvalue
FROM    letters
WHERE   rownum  BETWEEN 6 AND 10;

Output:

输出：

TEXTVALUE
---------
    f
    g
    h
    i
    j