MS SQL SERVER 分页

Question

我做了一个查询，查询语句如下：

SELECT DISTINCT m.logID 
FROM Monitor_data m 
inner join Monitor_object o on (o.objID = m.domainID)
inner join Monitor_event e on (e.mainID = m.logID)
WHERE (o.name = @objName
and m.service = @service
and e.statement = @statement
and m.start >= @start
and m.end <= @end)

现在我想要制作一个分页，但是之前的查询中不能使用LIMIT和OFFSET。我可以使用ROW_NUMBER，但是我不知道该怎么用 :/ 最好能够得到对于第n行到第m行的结果，这样我就能够轻松地创建分页过程了。

有人可以帮助我吗？谢谢。

- Simba

---

你正在使用哪个版本的SQL Server？OFFSET和FETCH是在2012年引入的。如果你正在运行2008R2，那么你现在的首要任务应该是迁移到其他版本，因为它将在一年内完全停止支持。 - alroc

我正在使用MS SQL 2012。谢谢您的答复，确实，OFFSET和FETCH很有效。但是我以前使用过PostgreSQL和MYSQL，当我说LIMIT和OFFSET时，我的意思是使用这样的查询：SELECT toto FROM babar LIMIT .. OFFSET ...（PostgreSQL），而mysql中的LIMIT允许直接执行此操作。 - Simba

Answer 1

使用@PageIndex和PageSize进行分页。

declare @PageIndex int=1
declare @PageSize int=10
declare @RecordCount int
SET NOCOUNT ON;
SELECT 
    ROW_NUMBER() OVER (ORDER BY m.logID ) as RowNumber, DISTINCT m.logID 
INTO #Results
FROM Monitor_data m 
    inner join Monitor_object o on (o.objID = m.domainID)
    inner join Monitor_event e on (e.mainID = m.logID)
WHERE (o.name = @objName
    and m.service = @service
    and e.statement = @statement
    and m.start >= @start
    and m.end <= @end)

SELECT @RecordCount = COUNT(*) FROM #Results

SELECT 
    *, @RecordCount as RecordCount  FROM #Results 
WHERE  
        RowNumber BETWEEN (@PageIndex -1) * @PageSize + 1 AND (((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1

DROP TABLE #Results

- Shailesh Chaudhary

Answer 2

尝试使用这个查询：

select logID from (
    SELECT DISTINCT m.logID,
                    ROW_NUMBER() over (order by m.start) rn
    FROM Monitor_data m 
    inner join Monitor_object o on (o.objID = m.domainID)
    inner join Monitor_event e on (e.mainID = m.logID)
    WHERE (o.name = @objName
           and m.service = @service
           and e.statement = @statement
           and m.start >= @start
           and m.end <= @end)
) a where rn between (m, n) --here you provide values for limits for rows to return

上述查询基于SQL Server中的ROW_NUMBER函数，该函数需要一些排序，因此我假设m.start将提供一个顺序（我认为它是开始日期或什么的 :)）。

- Michał Turczyn

---

没错！m.start确实是一个开始日期 :) 但我使用另一个键来排序它 ;) 谢谢。 - Simba

@Simba 好的，随你便 :) 如果答案帮到了你，请接受它（也可选择点赞）。 - Michał Turczyn

Answer 3

我想这就是你想要的。

Select logId from 
(SELECT DISTINCT m.logID, Row_number() over (order by (select null)) as ranking 
FROM Monitor_data m 
inner join Monitor_object o on (o.objID = m.domainID)
inner join Monitor_event e on (e.mainID = m.logID)
WHERE (o.name = @objName
and m.service = @service
and e.statement = @statement
and m.start >= @start
and m.end <= @end))
where ranking between n and m

- Ajay Gupta

Answer 4

按照******排序

跳过 @ItemsPerPage * (@CurrentPage - 1) 行

只获取接下来的 @ItemsPerPage 行

DECLARE @CurrentPage int = 1;
DECLARE @ItemsPerPage int = 10;

SELECT DISTINCT m.logID 
FROM Monitor_data m 
inner join Monitor_object o on (o.objID = m.domainID)
inner join Monitor_event e on (e.mainID = m.logID)
WHERE (o.name = @objName
and m.service = @service
and e.statement = @statement
and m.start >= @start
and m.end <= @end)


ORDER BY m.logID

OFFSET @ItemsPerPage * (@CurrentPage - 1) ROWS
FETCH NEXT @ItemsPerPage ROWS ONLY

分页示例

DECLARE @myTable TABLE(Id int, Name nvarchar(50),EventDate date);

INSERT INTO @myTable(Id, Name, EventDate)
VALUES (1, 'a', '2018-01-01'),
(2, 'b', '2018-01-02'),
(3, 'c', '2018-01-03'),
(4, 'd', '2018-01-04'),
(5, 'e', '2018-01-05'),
(6, 'f', '2018-01-06');


DECLARE @CurrentPage int = 1;
DECLARE @ItemsPerPage int = 4;


SELECT * FROM @myTable
ORDER BY EventDate  DESC
OFFSET @ItemsPerPage * (@CurrentPage - 1) ROWS
FETCH NEXT @ItemsPerPage ROWS ONLY

- giorgi02

---

太好了！谢谢。 - Simba

Answer 5

从2012年开始，您可以使用OFFSET和FETCH。在此之前，解决方案是使用ROW_NUMBER。但是请注意，ROW_NUMBER()方法非常缓慢。如果您没有性能问题，则可以使用它。

无论版本如何，使用TOP N并按所需列排序并同时指定最小值即可快速分页。例如：

select TOP (@pageSize) * from myTable
where myKeyValue > @minValue
order by myKeyValue;

- Cetin Basoz