SQL Server 2008 R2分页 [英] SQL Server 2008 R2 pagination

问题描述

我需要为联合查询实现分页，但是出现错误消息102，级别15，状态1，第14行 ')'附近的语法不正确.".我按照从链接.

I need to implement pagination for my union query, but I get error "Msg 102, Level 15, State 1, Line 14 Incorrect syntax near ')'.". I followed example I found from this link.

select * 
  from (select Id, 
               row_number() OVER (order by Id asc) as RowNumber 
          from (select Id 
                  from (select Id 
                          from Table1) as table1 
                union all 
                select Id 
                  from (select Id 
                          from Table2) as table2)) as t Derived 
  WHERE RowNumber > 5 
    and RowNumber <= 10

推荐答案

使用:

SELECT u.* 
  FROM (SELECT t.id, 
               ROW_NUMBER() OVER (ORDER BY t.id) as rownum 
          FROM (SELECT t1.id 
                  FROM TABLE1 t1
                UNION ALL 
                SELECT t2.id 
                  FROM TABLE2 t2) as t) AS u
 WHERE u.rownum > 5 
   AND u.rownum <= 10

在我看来，您的查询缺少名为"derived"的派生表的右括号，但是UNION中不需要子查询，因此我将其删除.

It looks to me that your query was missing a closing bracket for a derived table called "derived", but there wasn't the need for the subqueries in the UNION so I've removed them.

这篇关于SQL Server 2008 R2分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！