SqlServer 分頁 ROW_NUMBER() OVER(Order by * DESC ) AS RowNumber

語法如下：

select * from ( 

　　　　select *, ROW_NUMBER() OVER(Order by a.CreateTime DESC ) AS RowNumber from table_name as a 
　　) as b 

　　where RowNumber BETWEEN 1 and 5 

例項如下

select * from 
(
select ROW_NUMBER()over(order by DWHYBH ) as rowNo,* from  C60DWYHXX
) as t
where rowNo between 4 and 8
and t.DWJC like '%%'
and t.DWQC like '%%'

所用資料表為：

CREATE TABLE [dbo].[C60DWYHXX](
[DWHYBH] [varchar](20) NOT NULL primary key ,
[DWJC] [varchar](20) NOT NULL,
[DWQC] [varchar](60) NOT NULL,
[DWLB] [int] NOT NULL,
[FRDB] [dbo].[xm] NULL,
[FRDBZSH] [varchar](30) NULL,
[LXR] [dbo].[xm] NULL,
[LXRMOBILE] [dbo].[mobile] NULL,
[QYEMAIL] [varchar](50) NULL,
[DWLANDLINE] [varchar](15) NULL,
[QYWZDZ] [varchar](40) NULL,
[YHMM] [char](32) NULL,
[DJRQ] [date] NULL,
[UniqueID] [int] NOT NULL,
[wTag] [int] NULL,
[StrTag] [varchar](255) NULL,
[RecVersion] [bigint] NULL,
[YHID] [uniqueidentifier] NULL
)

第二部分：自己寫的拼接有省市縣三級拼接的SQL語句

SELECT ROW_NUMBER()OVER ( order by HYBH) AS rowNo ,b.XM




,(
select xzqhwm from Z21XZQH e where e.XZQH in (select SUBSTRING(f.xzqh,0,3)+'0000' from C02YHJBXX  f 
left join Z21XZQH d on f.XZQH=d.XZQH 
where f.XZQH=d.XZQH and f.YHID=a.YHID  )) as fir
,(
select xzqhwm from Z21XZQH e where e.XZQH in (select SUBSTRING(f.xzqh,0,5)+'00' from C02YHJBXX  f 
left join Z21XZQH d on f.XZQH=d.XZQH 
where f.XZQH=d.XZQH and f.YHID=a.YHID  )) as sec
,(
select XZQHWM 
from C02YHJBXX  c 
left join Z21XZQH d on c.XZQH=d.XZQH 
where c.XZQH=d.XZQH and c.YHID=a.YHID
) as thir


,b.XZQH,a.HYBH,a.YHID,a.UniqueID 
from C70HYGR a left join C02YHJBXX b on a.YHID=b.YHID
where 1=1 




--select SUBSTRING(XZQH,0,3)+'0000' from C02YHJBXX 
--select xzqh from C02YHJBXX

查詢結果如下圖所示

子查詢裡巢狀了in關鍵字。嘻嘻。效率應該不高。但是省的寫程式碼拼接datatable了