如何使用Microsoft SQL Server实现限制?

正在启动SQL SERVER 2005,您可以执行以下操作。。。

USE AdventureWorks;
GO
WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
    FROM Sales.SalesOrderHeader 
) 
SELECT * 
FROM OrderedOrders 
WHERE RowNumber BETWEEN 10 AND 20;

SELECT TOP 10 * FROM (SELECT TOP 20 FROM Table ORDER BY Id) ORDER BY Id DESC

SELECT TOP 10 * FROM table WHERE id NOT IN (SELECT TOP 10 id FROM table ORDER BY id) FROM table ORDER BY id

在我看来,MSSQL省略限制条款是犯罪行为。你不应该做这种笨拙的变通。

从SQL SERVER 2012开始,可以使用OFFSET FETCH子句:

USE AdventureWorks;
GO
SELECT SalesOrderID, OrderDate
FROM Sales.SalesOrderHeader 
ORDER BY SalesOrderID
    OFFSET 10 ROWS
    FETCH NEXT 10 ROWS ONLY;
GO

http://msdn.microsoft.com/en-us/library/ms188385(v=sql.110).aspx

当订单依据不唯一时,这可能无法正常工作。

如果将查询修改为ORDER BY OrderDate,则返回的结果集与预期不符。

以下是我在MS SQL Server 2012中限制结果的方式:

SELECT * 
FROM table1
ORDER BY columnName
  OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY

注: OFFSET ORDER BY .

解释代码行 OFFSET xx ROWS FETCH NEXT yy ROW ONLY

这个 xx

这个 yy 要从表中提取的记录/行数。

以上一个示例为基础:如果表1有40条记录,并且您开始从第10行提取,并获取下一组10条记录( ).

请查看链接以了解有关的更多信息 OFFSET

这几乎是我在10月份提出的一个问题的翻版: Emulate MySQL LIMIT clause in Microsoft SQL Server 2000

IDENTITY 主键。然后使用 BETWEEN 条件

ROW_NUMBER() 函数,因此可以得到相同的结果,但避免使用临时表。

SELECT t1.*
FROM (
    SELECT ROW_NUMBER OVER(ORDER BY id) AS row, t1.*
    FROM ( ...original SQL query... ) t1
) t2
WHERE t2.row BETWEEN @offset+1 AND @offset+@count;

您也可以将此作为 common table expression 如@Leon Tayson的 answer

SELECT  *
FROM    (
        SELECT  TOP 20
                t.*, ROW_NUMBER() OVER (ORDER BY field1) AS rn
        FROM    table1 t
        ORDER BY
                field1
        ) t
WHERE   rn > 10

MySQL LIMIT查询的语法如下:

SELECT * FROM table LIMIT OFFSET, ROW_COUNT

这可以像Microsoft SQL Server一样转换为Microsoft SQL Server

SELECT * FROM 
(
    SELECT TOP #{OFFSET+ROW_COUNT} *, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rnum 
    FROM table
) a
WHERE rnum > OFFSET

select * from table1 LIMIT 10,20 将是这样的:

SELECT * FROM 
(
    SELECT TOP 30 *, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rnum 
    FROM table1
) a
WHERE rnum > 10 

SELECT TOP 10 * FROM table;

SELECT * FROM table LIMIT 0,10;

Here's an article about implementing Limit in MsSQL 这本书读得很好,特别是评论。

这是我尽量避免使用MS Server的原因之一。。。但无论如何。有时候你就是没有选择(是的!我不得不使用过时的版本!!)。

我的建议是创建一个虚拟表:

SELECT * FROM table

致:

CREATE VIEW v_table AS    
    SELECT ROW_NUMBER() OVER (ORDER BY table_key) AS row,* FROM table

SELECT * FROM v_table WHERE row BETWEEN 10 AND 20

如果添加或删除字段,“行”将自动更新。

此选项的主要问题是ORDER BY是固定的。因此,如果需要不同的顺序,则必须创建另一个视图。

使现代化

这种方法还有一个问题:如果您试图过滤数据,它将无法按预期工作。例如,如果您这样做:

SELECT * FROM v_table WHERE field = 'test' AND row BETWEEN 10 AND 20

其中限制为10到20行中的数据(而不是搜索整个数据集并限制输出)。

我必须试一试。在下面的查询中,您可以看到分组依据、排序依据、跳过行和限制行。

select emp_no , sum(salary_amount) from emp_salary
Group by emp_no 
ORDER BY emp_no 
OFFSET 5 ROWS       -- Skip first 5 
FETCH NEXT 10 ROWS ONLY; -- limit to retrieve next 10 row after skiping rows

这是一种多步骤方法,将在SQL2000中使用。

-- Create a temp table to hold the data
CREATE TABLE #foo(rowID int identity(1, 1), myOtherColumns)

INSERT INTO #foo (myColumns) SELECT myData order By MyCriteria

Select * FROM #foo where rowID > 10

SELECT 
    * 
FROM 
    (
        SELECT 
            top 20              -- ($a) number of records to show
            * 
        FROM
            (
                SELECT 
                    top 29      -- ($b) last record position
                    * 
                FROM 
                    table       -- replace this for table name (i.e. "Customer")
                ORDER BY 
                    2 ASC
            ) AS tbl1 
        ORDER BY 
            2 DESC
    ) AS tbl2 
ORDER BY 
    2 ASC;

-- Examples:

-- Show 5 records from position 5:
-- $a = 5;
-- $b = (5 + 5) - 1
-- $b = 9;

-- Show 10 records from position 4:
-- $a = 10;
-- $b = (10 + 4) - 1
-- $b = 13;

-- To calculate $b:
-- $b = ($a + position) - 1

-- For the present exercise we need to:
-- Show 20 records from position 10:
-- $a = 20;
-- $b = (20 + 10) - 1
-- $b = 29;

MYSQL:

SELECT 'filds' FROM 'table' WHERE 'where' LIMIT 'offset','per_page'

MSSQL:

SELECT 'filds' FROM 'table' WHERE 'where' ORDER BY 'any' OFFSET 'offset' 
ROWS FETCH NEXT 'per_page' ROWS ONLY

在SQL中,没有LIMIT关键字。如果只需要有限的行数,则应使用类似于限制的TOP关键字。

若您的ID是唯一标识符类型或表中的ID未排序,则必须执行以下操作。

select * from
(select ROW_NUMBER() OVER (ORDER BY (select 0)) AS RowNumber,* from table1) a
where a.RowNumber between 2 and 5

select * from limit 2,5

SELECT * FROM
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) as [Count], * FROM table1
) as a
WHERE [Count] BETWEEN 10 and 20;

--给出一列[Count],并为每一行分配一个唯一的计数,而无需排序,然后重新选择可以提供限制的位置:)

其中一个可能的方式得到如下结果,希望这将有帮助。

declare @start int
declare @end int
SET @start = '5000';  -- 0 , 5000 ,
SET @end = '10000'; -- 5001, 10001
SELECT * FROM ( 
  SELECT TABLE_NAME,TABLE_TYPE, ROW_NUMBER() OVER (ORDER BY TABLE_NAME) as row FROM information_schema.tables
 ) a WHERE a.row > @start and a.row <= @end

如果我没记错的话(我已经有一段时间没有接触SQL Server了),您可能可以使用以下内容:(2005年及以后)

SELECT
    *
   ,ROW_NUMBER() OVER(ORDER BY SomeFields) AS [RowNum]
FROM SomeTable
WHERE RowNum BETWEEN 10 AND 20