等效于 T-SQL 中的 LIMIT [英] Equivalent of LIMIT in T-SQL

问题描述

在 PHP 中，我正在执行需要 (LIMIT) 类型功能的 SQL Server 查询.我已经知道 TOP 通常用于 SQL Server 而不是 LIMIT(这是 MySQL 语法).但是，TOP 不允许您像 LIMIT 那样设置偏移".在我的查询中，我需要动态的偏移量"和行数".示例:

In PHP I'm doing a SQL Server query where I need (LIMIT) type functionality. I already know that TOP is typically used in SQL Server instead of LIMIT (which is MySQL syntax). However, TOP doesn't allow you to set on 'offset' the way LIMIT does. In my query, I will need the 'offset' and 'number of rows' to be dynamic. Example:

LIMIT $startRow, $maxRow

有什么想法或建议吗?

推荐答案

Sql Server 2005 没有你上面的限制语法，直到 2012 年才添加.你需要使用 OVER 子句和公共表表达.

Sql Server 2005 doesn't have the limit syntax you have above, that wasn't added until 2012. You will want to use the OVER clause and common table expressions.

来自 SqlTeam 的示例

WITH Members  AS
(
    SELECT  M_NAME, M_POSTS, M_LASTPOSTDATE, M_LASTHEREDATE, M_DATE, M_COUNTRY,
            ROW_NUMBER() OVER (ORDER BY M_POSTS DESC) AS RowNumber
    FROM    dbo.FORUM_MEMBERS
)
SELECT  RowNumber, M_NAME, M_POSTS, M_LASTPOSTDATE, M_LASTHEREDATE, M_DATE, M_COUNTRY
FROM    Members
WHERE   RowNumber BETWEEN 1 AND 20
ORDER BY RowNumber ASC;

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