与 SQL SERVER 等效的 MySQL LIMIT 子句

2021-11-20 00:00:00 mysql sql-server

我一直在阅读有关 SQL SERVER LIMIT 子句的替代方案的大量资料.令人沮丧的是他们仍然拒绝适应它.无论如何，我真的无法解决这个问题.我要转换的查询是这样的...

I've been doing a lot of reading on alternatives to the LIMIT clause for SQL SERVER. It's so frustrating that they still refuse to adapt it. Anyway, I really havn't been able to get my head around this. The query I'm trying to convert is this...

SELECT ID, Name, Price, Image FROM Products ORDER BY ID ASC LIMIT $start_from, $items_on_page

如有任何帮助，我们将不胜感激，谢谢.

Any assistance would be much appreciated, thank you.

推荐答案

在 SQL Server 2012 中，支持 ANSI 标准 OFFSET/FETCH 语法.我写了关于这个的博客 这里是 官方文档(这是ORDER BY 的扩展).您为 SQL Server 2012 转换的语法是:

In SQL Server 2012, there is support for the ANSI standard OFFSET / FETCH syntax. I blogged about this and here is the official doc (this is an extension to ORDER BY). Your syntax converted for SQL Server 2012 would be:

SELECT ID, Name, Price, Image 
  FROM Products 
  ORDER BY ID ASC 
  OFFSET (@start_from - 1) ROWS -- not sure if you need -1
    -- because I don't know how you calculated @start_from
  FETCH NEXT @items_on_page ROWS ONLY;

在此之前，您需要使用各种解决方法，包括 ROW_NUMBER() 方法.请参阅这篇文章和后续讨论.如果您不是在 SQL Server 2012 上，则不能使用标准语法或 MySQL 的非标准 LIMIT，但可以使用更详细的解决方案，例如:

Prior to that, you need to use various workarounds, including the ROW_NUMBER() method. See this article and the follow-on discussion. If you are not on SQL Server 2012, you can't use standard syntax or MySQL's non-standard LIMIT but you can use a more verbose solution such as:

;WITH o AS
(
    SELECT TOP ((@start_from - 1) + @items_on_page)
         -- again, not sure if you need -1 because I 
         -- don't know how you calculated @start_from
      RowNum = ROW_NUMBER() OVER (ORDER BY ID ASC)
      /* , other columns */
    FROM Products
)
SELECT 
    RowNum
    /* , other columns */
FROM
    o
WHERE
    RowNum >= @start_from
ORDER BY
    RowNum;

有很多其他方法可以给这只猫剥皮，这不太可能是最有效的，但语法方面可能是最简单的.我建议查看我发布的链接以及问题评论中提到的重复建议.

There are many other ways to skin this cat, this is unlikely to be the most efficient but syntax-wise is probably simplest. I suggest reviewing the links I posted as well as the duplicate suggestions noted in the comments to the question.

相关文章