SQL SERVER的MySQL LIMIT子句等效 [英] MySQL LIMIT clause equivalent for 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

非常感谢您的协助.

推荐答案

在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()方法.请参见本文和

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.

这篇关于SQL SERVER的MySQL LIMIT子句等效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！