[sql server]后台分页，震惊！最后一种竟然...

闲的没事想练习一下

要分页的数据：

select * from 表1

给她而设置个别名，为这个结果集的最前面加上序号

select 
        row_number()over(order by 表1.id asc) as numberId
        ,表1.* 
from (
        select * from 表1
       ) as 表1

最后选出前20

select top 20 biao1.* 
  from (
        select 
                row_number()over(order by 表1.id asc) as numberId
                ,表1.* 
          from (
                select * from 表1
               ) as 表1
        )as biao1
----------------------------------------------------------------------------
-- 选出了前20个，但是如果想分页就得限制更多
select biao1.* 
  from (
        select 
                row_number()over(order by 表1.id asc) as numberId
                ,表1.* 
          from (
                select * from 表1
               ) as 表1
        )as biao1 
where biao1 .numberId between 0 and 20
-- 这个0和20就可以是一个变量

但是感觉现在写的有点麻烦就简化一下

select
  biao1.* 
from
  ( 
    select
      row_number() over (order by 表1.id asc) as numberId
      , 表1.* 
    from
      表1
  ) as biao1
where
  numberId between 0 and 20

但是我还是想再简单一点，但是这么写就报错了，因为执行的from>where>gorup by>having>select|top|distinct >order by

select
  row_number() over (order by 表1.id asc) as numberId
  , 表1.* 
from
  表1
where
  numberId between 0 and 20

在和这个时候numberId还不存在，所以就报错了,目前只想到这么写。

select biao1.*
from 
(select
  row_number() over (order by 表1.id asc) as numberId
  , 表1.* 
from
  表1)as biao1
where
  numberId between 0 and 20