首页 >数据库 >mysql教程 >SqlServer 分页 ROW

SqlServer 分页 ROW

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB原创
2016-06-07 15:34:12981浏览

语法如下: 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


语法如下:

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
)

SqlServer 分页  ROW


第二部分:自己写的拼接有省市县三级拼接的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


查询结果如下图所示

SqlServer 分页  ROW


子查询里嵌套了in关键字。嘻嘻。效率应该不高。但是省的写代码拼接datatable了

声明:
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn