GVKun编程网logo

SQL Server 得到行号的SQL（sqlserver获取行号）

21

对于SQLServer得到行号的SQL感兴趣的读者，本文将提供您所需要的所有信息，我们将详细讲解sqlserver获取行号，并且为您提供关于DataGear变更部署数据库为SQLServer填坑指南（

对于SQL Server 得到行号的SQL感兴趣的读者，本文将提供您所需要的所有信息，我们将详细讲解sqlserver获取行号，并且为您提供关于DataGear 变更部署数据库为SQL Server填坑指南（含转写后的SQL server代码及SQL server配置文件）、Kubernetes SQL Server或Azure SQL Server PaaS Azure SQL数据库容器化的SQL Server、SQL Server - SQL Server 2019 启用SQL Server验证Super Administrator(sa)用户登录、SQL Server SQL行号排序和分页(SQL查询中插入行号 自定义分页的另类实现)的宝贵知识。

本文目录一览：

SQL Server 得到行号的SQL（sqlserver获取行号）

SQL Server 得到行号的SQL（sqlserver获取行号）

使用临时表：

select   id=identity(int,1,1),value   into   #temp   from   YourTable  
  select   *   from   #temp  
  drop   table   #temp  

 

取得第11到20行记录：

select   IDENTITY(int,   1,1)   AS   ID_Num,*   into   #temp   from   表  
  select   *   from   #temp   where   ID_Num>10   and   ID_Num<=20

 

或  
  SELECT   Top   @PageSize   *  
            FROM   T  
          WHERE   SortField   NOT   IN   (SELECT   TOP   @PageSize   *   @Pagei   SortField  
                                                              FROM   T  
                                                      ORDER   BY   SortField  
                                                          )  
    ORDER   BY   SortField

 

REF:http://topic.csdn.net/t/20021022/21/1116380.html

 

DataGear 变更部署数据库为SQL Server填坑指南（含转写后的SQL server代码及SQL server配置文件）

DataGear 变更部署数据库为SQL Server填坑指南（含转写后的SQL server代码及SQL server配置文件）

  • 1. 引言
  • 2. 配置数据库链接
  • 3. 引入数据库驱动
  • 4. 手动初始化数据库
  • 5. 改写SQL
  • 6. 其他
  • 7. 参考

1. 引言

DataGear默认使用Derby数据库作为系统的元数据库，至于待分析的业务库则支持MySQL、SQL Server等。
Derby数据库足以支持系统的数据集、图表、看板等对象的管理，且不需要安装数据库服务，但由于目前DataGear不支持单点登录集成，因此项目上为了便于用户认证信息的统一管理，以及DataGear数据库的备份等，将Derby变更为SQL Server，与待分析的业务数据库一致。
在变更操作过程中，参考了DataGear官方说明，操作时遇到一些坑，记录下来分享与大家。
不少内容是引用官方文档的，直接抄来了。
datagear架构图
gitee地址：
https://gitee.com/datagear/datagear

2. 配置数据库链接

在config/application.properties添加datasource.driverClassName、datasource.url、datasource.username、datasource.password配置项，配置新数据库连接。

  • MySQL
datasource.driverClassName=com.mysql.cj.jdbc.Driverdatasource.url=jdbc:mysql://localhost:3306/datageardatasource.username=datageardatasource.password=datagear
  • SQL Server
datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriverdatasource.url=jdbc:sqlserver://192.168.1.30:1433;SelectMethod=cursor;DatabaseName=datageardatasource.username=datageardatasource.password=datagear

3. 引入数据库驱动

在根目录下新建lib包，将JDBC驱动（sqljdbc4-4.0.jar）放入。
修改startup.bat为

java -Xbootclasspath/a:lib/sqljdbc4-4.0.jar -jar datagear-2.4.0.jar

4. 手动初始化数据库

此时运行startup，datagear将会按照配置的链接访问数据库，如果数据库配置以及驱动无误，则会进入下一步。
接着datagear检测到没有数据库，此时会自动创建数据库，使用的SQL是打包在jar文件中的，源码库路径为datagear-management/src/main/resources/org/datagear/management/ddl/datagear.sql。此SQL为Derby数据库，因此将执行失败。
扩展：可以用SQL server等数据库脚本替换了源码sql并打包，分发到项目上，项目上直接执行即可创建数据库，类似于Activity等的部署。
接着需要先改写Derby建库脚本为SQL server，改写后执行SQL，然后设置版本号，然后运行startup.bat即可。
坑：如果是新环境，DATAGEAR_VERSION（版本）表中的记录为空，此时按照官方说明执行SQL设置版本号将不起效。

UPDATE DATAGEAR_VERSION SET VERSION_VALUE=''当前版本号''

或许Derby支持数据不存在则insert的update骚操作，对于SQL server需要insert下。
此坑造成的效果时，启动时dategear查询到当前已有数据库，但版本为null，因此需要升级，且从头升级，于是执行打包的SQL。

5. 改写SQL

DataGear的（升级）SQL脚本非常漂亮，每个版本不论是否有SQL更新，都写有注释，供手工和自动执行时参考。

-------------------------------------------version[1.6.1], DO NOT EDIT THIS LINE!-----------------------------------------

如果是新环境，则需要从头执行到尾，如果是升级，则找到起始版本执行后边的SQL即可。

以下列举Derby转SQL server的几个常见操作。

  • TIMESTAMP，巨坑，SQL server也有该数据类型，但意义不一样，改为datetime
  • CURRENT_TIMESTAMP，当前时间，修改为(getdate())，获取当前时间
  • 函数按照SQLserver语法改写，变量填写@即可。函数实现Derby不支持的replace、取余操作，datagear采用了java扩展，SQL server直接用系统函数、操作符即可，不需要.net扩展
  • DATAGEAR_REPLACEREGEX，按官方说明，不必理睬即可，该正则表达式替换新版本中已废弃
  • RENAME COLUMN ，重命名列，按照SQL server语法替换
  • RENAME TABLE ，重命名表，按照SQL server语法替换
  • 表中加字段，ADD COLUMN 去掉column
  • ALTER COLUMN DS_TYPE SET NOT NULL;，修改列非空，SQL server需要找到原来的类型，一并修改。
  • VARCHAR(10000)，SQL server支持到8K，修改为VARCHAR(8000)

修改后的SQL Server版SQL为：

-------------------------------------------version[1.0.0], DO NOT EDIT THIS LINE!-----------------------------------------if not exists (select * from sysobjects where name=''DATAGEAR_VERSION'' and xtype=''U'')CREATE TABLE DATAGEAR_VERSION (	VERSION_MAJOR VARCHAR(50),	VERSION_MINOR VARCHAR(50),	VERSION_REVISION VARCHAR(50),	VERSION_BUILD VARCHAR(50));goif not exists (select * from sysobjects where name=''DATAGEAR_USER'' and xtype=''U'')CREATE TABLE DATAGEAR_USER(	USER_ID VARCHAR(50) NOT NULL,	USER_NAME VARCHAR(50) NOT NULL,	USER_PASSWORD VARCHAR(200) NOT NULL,	USER_REAL_NAME VARCHAR(100),	USER_EMAIL VARCHAR(200),	USER_IS_ADMIN VARCHAR(20),	USER_CREATE_TIME DATETIME,	PRIMARY KEY (USER_ID),	UNIQUE (USER_NAME));go--alter table DATAGEAR_USER alter column USER_CREATE_TIME datetime;--无法更改列 ''USER_CREATE_TIME''，因为它是 ''timestamp''。--alter table DATAGEAR_USER drop column USER_CREATE_TIME;--alter table DATAGEAR_USER add USER_CREATE_TIME DATETIME;--the password is ''admin''INSERT INTO DATAGEAR_USER VALUES(''admin'', ''admin'', ''4c6d8d058a4db956660f0ee51fcb515f93471a086fc676bfb71ba2ceece5bf4702c61cefab3fa54b'', '''', '''', ''true'', default);if not exists (select * from sysobjects where name=''DATAGEAR_SCHEMA'' and xtype=''U'')CREATE TABLE DATAGEAR_SCHEMA(	SCHEMA_ID VARCHAR(50) NOT NULL,	SCHEMA_TITLE VARCHAR(100) NOT NULL,	SCHEMA_URL VARCHAR(200) NOT NULL,	SCHEMA_USER VARCHAR(200),	SCHEMA_PASSWORD VARCHAR(200),	SCHEMA_CREATE_USER_ID VARCHAR(50),	SCHEMA_CREATE_TIME DATETIME,	SCHEMA_SHARED VARCHAR(20),	DRIVER_ENTITY_ID VARCHAR(100),	PRIMARY KEY (SCHEMA_ID));--alter table DATAGEAR_SCHEMA drop column SCHEMA_CREATE_TIME;--alter table DATAGEAR_SCHEMA add SCHEMA_CREATE_TIME DATETIME;-------------------------------------------version[1.1.0], DO NOT EDIT THIS LINE!------------------------------------------------------------------------------------version[1.1.1], DO NOT EDIT THIS LINE!------------------------------------------------------------------------------------version[1.2.0], DO NOT EDIT THIS LINE!------------------------------------------------------------------------------------version[1.3.0], DO NOT EDIT THIS LINE!------------------------------------------------------------------------------------version[1.4.0], DO NOT EDIT THIS LINE!-----------------------------------------if not exists (select * from sysobjects where name=''DATAGEAR_ROLE'' and xtype=''U'')CREATE TABLE DATAGEAR_ROLE(	ROLE_ID VARCHAR(50) NOT NULL,	ROLE_NAME VARCHAR(100) NOT NULL,	ROLE_DESCRIPTION VARCHAR(200),	ROLE_ENABLED VARCHAR(10) NOT NULL,	ROLE_CREATE_TIME DATETIME,	PRIMARY KEY (ROLE_ID));--alter table DATAGEAR_ROLE drop column ROLE_CREATE_TIME;--alter table DATAGEAR_ROLE add ROLE_CREATE_TIME DATETIME;if not exists (select * from sysobjects where name=''DATAGEAR_ROLE_USER'' and xtype=''U'')CREATE TABLE DATAGEAR_ROLE_USER(	RU_ID VARCHAR(50) NOT NULL,	RU_ROLE_ID VARCHAR(50) NOT NULL,	RU_USER_ID VARCHAR(50) NOT NULL,	PRIMARY KEY (RU_ID));ALTER TABLE DATAGEAR_ROLE_USER ADD FOREIGN KEY (RU_ROLE_ID) REFERENCES DATAGEAR_ROLE (ROLE_ID) ON DELETE CASCADE;ALTER TABLE DATAGEAR_ROLE_USER ADD FOREIGN KEY (RU_USER_ID) REFERENCES DATAGEAR_USER (USER_ID) ON DELETE CASCADE;ALTER TABLE DATAGEAR_ROLE_USER ADD CONSTRAINT UK_RU_ROLE_USER_ID UNIQUE (RU_ROLE_ID, RU_USER_ID);if not exists (select * from sysobjects where name=''DATAGEAR_AUTHORIZATION'' and xtype=''U'')CREATE TABLE DATAGEAR_AUTHORIZATION(	AUTH_ID VARCHAR(50) NOT NULL,	AUTH_RESOURCE VARCHAR(200) NOT NULL,	AUTH_RESOURCE_TYPE VARCHAR(50) NOT NULL,	AUTH_PRINCIPAL VARCHAR(200) NOT NULL,	AUTH_PRINCIPAL_TYPE VARCHAR(50) NOT NULL,	AUTH_PERMISSION SMALLINT NOT NULL,	AUTH_ENABLED VARCHAR(10) NOT NULL,	AUTH_CREATE_TIME DATETIME,	AUTH_CREATE_USER_ID VARCHAR(50),	PRIMARY KEY (AUTH_ID));--alter table DATAGEAR_AUTHORIZATION drop column AUTH_CREATE_TIME;--alter table DATAGEAR_AUTHORIZATION add AUTH_CREATE_TIME DATETIME;go--自定义REPLACE函数--srcStr 原始字符串--oldStr 要替换的旧子串--newStr 要替换的新子串CREATE FUNCTION DATAGEAR_REPLACE(@srcStr VARCHAR(500), @oldStr VARCHAR(100), @newStr VARCHAR(100)) RETURNS VARCHAR(500)asbegin--PARAMETER STYLE JAVA NO SQL LANGUAGE JAVA EXTERNAL NAME ''org.datagear.management.util.DerbyFunctionSupport.replace'';	return replace(@srcStr,@oldStr,@newStr);end;goif not exists (select * from sysobjects where name=''DATAGEAR_SQL_HISTORY'' and xtype=''U'')CREATE TABLE DATAGEAR_SQL_HISTORY(	SQLHIS_ID VARCHAR(50) NOT NULL,	SQLHIS_SQL VARCHAR(5000) NOT NULL,	SQLHIS_SCHEMA_ID VARCHAR(50) NOT NULL,	SQLHIS_USER_ID VARCHAR(50) NOT NULL,	SQLHIS_CREATE_TIME DATETIME DEFAULT GETDATE(),	PRIMARY KEY (SQLHIS_ID));--alter table DATAGEAR_SQL_HISTORY drop column SQLHIS_CREATE_TIME;--alter table DATAGEAR_SQL_HISTORY add SQLHIS_CREATE_TIME DATETIME;ALTER TABLE DATAGEAR_SQL_HISTORY ADD FOREIGN KEY (SQLHIS_SCHEMA_ID) REFERENCES DATAGEAR_SCHEMA (SCHEMA_ID) ON DELETE CASCADE;-------------------------------------------version[1.5.0], DO NOT EDIT THIS LINE!-------------------------------------------SQL数据集if not exists (select * from sysobjects where name=''DATAGEAR_SQL_DATA_SET'' and xtype=''U'')CREATE TABLE DATAGEAR_SQL_DATA_SET(	DS_ID VARCHAR(50) NOT NULL,	DS_NAME VARCHAR(100) NOT NULL,	DS_SCHEMA_ID VARCHAR(50) NOT NULL,	DS_SQL VARCHAR(1000) NOT NULL,	DS_CREATE_USER_ID VARCHAR(50),	DS_CREATE_TIME DATETIME,	PRIMARY KEY (DS_ID));-- alter table DATAGEAR_DATA_SET drop column DS_CREATE_TIME;-- alter table DATAGEAR_DATA_SET add DS_CREATE_TIME DATETIME;--该字段后需要删除，约束将限制其删除--ALTER TABLE DATAGEAR_SQL_DATA_SET ADD FOREIGN KEY (DS_SCHEMA_ID) REFERENCES DATAGEAR_SCHEMA (SCHEMA_ID);--数据集属性if not exists (select * from sysobjects where name=''DATAGEAR_DATA_SET_PROP'' and xtype=''U'')CREATE TABLE DATAGEAR_DATA_SET_PROP(	PROP_DS_ID VARCHAR(50) NOT NULL,	PROP_NAME VARCHAR(100) NOT NULL,	PROP_TYPE VARCHAR(50) NOT NULL,	PROP_LABEL VARCHAR(100),	PROP_ORDER INTEGER);ALTER TABLE DATAGEAR_DATA_SET_PROP ADD FOREIGN KEY (PROP_DS_ID) REFERENCES DATAGEAR_SQL_DATA_SET (DS_ID) ON DELETE CASCADE;ALTER TABLE DATAGEAR_DATA_SET_PROP ADD CONSTRAINT UK_DS_PROP_DS_ID_NAME UNIQUE (PROP_DS_ID, PROP_NAME);--数据集参数if not exists (select * from sysobjects where name=''DATAGEAR_DATA_SET_PAR'' and xtype=''U'')CREATE TABLE DATAGEAR_DATA_SET_PAR(	PAR_DS_ID VARCHAR(50) NOT NULL,	PAR_NAME VARCHAR(100) NOT NULL,	PAR_TYPE VARCHAR(100) NOT NULL,	PAR_REQUIRED VARCHAR(10),	PAR_DEFAULT_VALUE VARCHAR(200),	PAR_ORDER INTEGER);ALTER TABLE DATAGEAR_DATA_SET_PAR ADD FOREIGN KEY (PAR_DS_ID) REFERENCES DATAGEAR_SQL_DATA_SET (DS_ID) ON DELETE CASCADE; ALTER TABLE DATAGEAR_DATA_SET_PAR ADD CONSTRAINT UK_DS_PAR_DS_ID_NAME UNIQUE (PAR_DS_ID, PAR_NAME);--数据集输出if not exists (select * from sysobjects where name=''DATAGEAR_DATA_SET_EXPT'' and xtype=''U'')CREATE TABLE DATAGEAR_DATA_SET_EXPT(	EXPT_DS_ID VARCHAR(50) NOT NULL,	EXPT_NAME VARCHAR(100) NOT NULL,	EXPT_TYPE VARCHAR(50) NOT NULL,	EXPT_ORDER INTEGER);ALTER TABLE DATAGEAR_DATA_SET_EXPT ADD FOREIGN KEY (EXPT_DS_ID) REFERENCES DATAGEAR_SQL_DATA_SET (DS_ID) ON DELETE CASCADE;ALTER TABLE DATAGEAR_DATA_SET_EXPT ADD CONSTRAINT UK_DS_EXPT_DS_ID_NAME UNIQUE (EXPT_DS_ID, EXPT_NAME);--图表if not exists (select * from sysobjects where name=''DATAGEAR_HTML_CHART_WIDGET'' and xtype=''U'')CREATE TABLE DATAGEAR_HTML_CHART_WIDGET(	HCW_ID VARCHAR(50) NOT NULL,	HCW_NAME VARCHAR(100) NOT NULL,	HCW_PLUGIN_ID VARCHAR(100) NOT NULL,	HCW_UPDATE_INTERVAL INTEGER,	HCW_CREATE_USER_ID VARCHAR(50),	HCW_CREATE_TIME DATETIME,	PRIMARY KEY (HCW_ID));-- alter table DATAGEAR_HTML_CHART_WIDGET drop column HCW_CREATE_TIME;-- alter table DATAGEAR_HTML_CHART_WIDGET add HCW_CREATE_TIME DATETIME;--图表-数据集信息if not exists (select * from sysobjects where name=''DATAGEAR_HCW_DS'' and xtype=''U'')CREATE TABLE DATAGEAR_HCW_DS(	HCW_ID VARCHAR(50) NOT NULL,	DS_ID VARCHAR(50) NOT NULL,	DS_PROPERTY_SIGNS VARCHAR(500),	DS_ORDER INTEGER);ALTER TABLE DATAGEAR_HCW_DS ADD FOREIGN KEY (HCW_ID) REFERENCES DATAGEAR_HTML_CHART_WIDGET (HCW_ID) ON DELETE CASCADE;ALTER TABLE DATAGEAR_HCW_DS ADD FOREIGN KEY (DS_ID) REFERENCES DATAGEAR_SQL_DATA_SET (DS_ID) ON DELETE CASCADE;--看板if not exists (select * from sysobjects where name=''DATAGEAR_HTML_DASHBOARD'' and xtype=''U'')CREATE TABLE DATAGEAR_HTML_DASHBOARD(	HD_ID VARCHAR(50) NOT NULL,	HD_NAME VARCHAR(100) NOT NULL,	HD_TEMPLATE VARCHAR(100) NOT NULL,	HD_TEMPLATE_ENCODING VARCHAR(50),	HD_CREATE_USER_ID VARCHAR(50),	HD_CREATE_TIME DATETIME,	PRIMARY KEY (HD_ID));-- alter table DATAGEAR_HTML_DASHBOARD drop column HD_CREATE_TIME;-- alter table DATAGEAR_HTML_DASHBOARD add HD_CREATE_TIME DATETIME;-------------------------------------------version[1.6.0], DO NOT EDIT THIS LINE!-------------------------------------------ALTER TABLE DATAGEAR_HTML_DASHBOARD ALTER COLUMN HD_TEMPLATE SET DATA TYPE VARCHAR(500);-------------------------------------------version[1.6.1], DO NOT EDIT THIS LINE!------------------------------------------------------------------------------------version[1.7.0], DO NOT EDIT THIS LINE!-------------------------------------------自定义正则REPLACE函数，目前仅用于下面修改数据标记--srcStr 原始字符串--oldStr 要替换的正则表达式--newStr 要替换的新子串--CREATE FUNCTION DATAGEAR_REPLACEREGEX(srcStr VARCHAR(500), oldStr VARCHAR(100), newStr VARCHAR(100)) RETURNS VARCHAR(500)--PARAMETER STYLE JAVA NO SQL LANGUAGE JAVA EXTERNAL NAME ''org.datagear.management.util.DerbyFunctionSupport.replaceRegex''; --扩充SQL字段ALTER TABLE DATAGEAR_SQL_DATA_SET ALTER COLUMN DS_SQL VARCHAR(8000);-------------------------------------------version[1.8.0], DO NOT EDIT THIS LINE!-------------------------------------------默认值字段改为描述字段--RENAME COLUMN DATAGEAR_DATA_SET_PAR.PAR_DEFAULT_VALUE TO PAR_DESC;exec sp_rename ''DATAGEAR_DATA_SET_PAR.PAR_DEFAULT_VALUE'',''PAR_DESC'' --移除数据集输出项表DROP TABLE DATAGEAR_DATA_SET_EXPT;--扩充图表数据集数据标记列ALTER TABLE DATAGEAR_HCW_DS ALTER COLUMN DS_PROPERTY_SIGNS VARCHAR(1000);--添加图表数据集别名列ALTER TABLE DATAGEAR_HCW_DS ADD DS_ALIAS VARCHAR(100);--添加图表数据集参数值列ALTER TABLE DATAGEAR_HCW_DS ADD DS_PARAM_VALUES VARCHAR(1000);-------------------------------------------version[1.8.1], DO NOT EDIT THIS LINE!------------------------------------------------------------------------------------version[1.9.0], DO NOT EDIT THIS LINE!----------------------------------------- -------------------------------------------version[1.10.0], DO NOT EDIT THIS LINE!-------------------------------------------添加数据集参数输入框类型ALTER TABLE DATAGEAR_DATA_SET_PAR ADD PAR_INPUT_TYPE VARCHAR(50);--添加数据集参数输入框载荷ALTER TABLE DATAGE

Kubernetes SQL Server或Azure SQL Server PaaS Azure SQL数据库容器化的SQL Server

Kubernetes SQL Server或Azure SQL Server PaaS Azure SQL数据库容器化的SQL Server

我将发表评论作为答案。

Azure SQL数据库

这是一项服务，可为您管理数据库的艰苦工作。 包括：

  • 备份和还原
  • 高可用性
  • 数据复制
  • 缩放
  • 选择合适的硬件
  • 始终保持最新状态，如果推出新版本，则可以提高兼容性级别，并可以使用新功能
  • 安全性
  • Azure AD集成
  • 索引建议
  • 弹性池
  • 天蓝色支持
  • 许可

您在Azure SQL数据库中无法做的事情

  • CLR：加载可从SQL Server执行的自定义.NET代码。
  • 跟踪标记
  • SQL代理作业（Elastic database jobs在预览中）
  • 更改数据捕获（cdc）
  • 跨数据库事务
  • 数据库邮件
  • 附加数据库
  • BACKUP或RESTORE语句
  • 更改排序规则
  • 镜像
  • 快照
  • 一些DBCC语句
  • 一些DMV的
  • 链接服务器
  • 批量导入时的最小日志记录
  • OpenQuery / OpenDataSource
  • 资源总监
  • 服务经纪人
  • ...

请参见this comparison between Azure SQL Databases and Azure SQL Managed instances.

List of T-SQL differences

容器化的SQL Server

您只能运行Linux版本的SQL Server。其中仍然存在大量问题。 Azure照顾的所有事情，您需要做好自己的工作。它很新，所以您可能遇到一些以前从未见过的问题，但是社区正在不断发展。您还需要购买SQL Server许可证。

何时应使用容器化的SQL Server？ 您想与云无关，能够切换云提供商。

目前，我不想在容器化的SQL Server上运行生产工作负载。当SQL Server Linux变得成熟时，这将会改变。

SQL Server - SQL Server 2019 启用SQL Server验证Super Administrator(sa)用户登录

SQL Server - SQL Server 2019 启用SQL Server验证Super Administrator(sa)用户登录

SQL Server 2019 启用SQL Server验证Super Administrator(sa)用户登录

 

在项目开发过程中通常连接SQL Server时需要用SQL Server方式，所以我们需要先开启这种验证方式，然后再根据需要添加项目用户，设定相关权限。

先用Windows身份验证方式连接数据库，然后按照如下几步设置，完成后重启服务，切换验证方式即可使用sa登录。

 

  作者：Jeremy.Wu
  出处：https://www.cnblogs.com/jeremywucnblog/
  本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。

SQL Server SQL行号排序和分页(SQL查询中插入行号 自定义分页的另类实现)

SQL Server SQL行号排序和分页(SQL查询中插入行号 自定义分页的另类实现)

感兴趣的小伙伴，下面一起跟随小编 jb51.cc的小编两巴掌来看看吧！

（一）行号显示和排序

1.sql Server的行号

A.sql 2000使用identity(int,1,1)和临时表，可以显示行号
SELECT
identity(int,1) AS ROWNUM,
[DataID]
INTO #1
FROM DATAS
order by DataID;
SELECT * FROM #1
B.sql 2005提供一个很好用的函数row_number()，
可以直接用来显示行号，当然也可以使用sql 2000的identity
SELECT
row_number()over(ORDER BY DataID) AS ROWNUM,
[DataID]
FROM DATAS;
这里如果添加排序功能，则先排序再添加行号

2.ORACLE的行号显示

使用ROWNUM
SELECT
ROWNUM,
[DataID]
FROM DATAS
order by DataID
注意：先加行号再排序,如果想排序好再加行号就要使用子查询

3.取前n条数据
A.sql版
select top n [DataID] from DATAS
B.ORACLE版
SELECT
[DataID]
FROM DATAS where ROWNUM<=n
其中，n>=1
ORACLE的ROWNUM不能应用于大于，只能 ROWNUM= 1,或者<= 大于1 的自然数

(二)sql分页的几种方式
以每页10条数据为例，查询第三页数据，即21-30这些记录
1.分页方案一：(利用Not In和SELECT TOP分页)
语句形式：

代码如下:

 
SELECT TOP 10 * 
FROM DATAS 
WHERE DataID NOT IN 
(SELECT TOP 20 DataID 
FROM DATAS 
ORDER BY DataID) 
ORDER BY DataID

2.分页方案二：(利用ID大于多少和SELECT TOP分页）
语句形式：

代码如下:

 
SELECT TOP 10 * 
FROM DATAS 
WHERE ID > 
(SELECT MAX(DataID) 
FROM (SELECT TOP 20 DataID 
FROM DATAS 
ORDER BY DataID) AS T) 
ORDER BY DataID

3.分页方案三

代码如下:

 
select top 10 DataID from 
(SELECT top 30 
[DataID] 
FROM DATAS 
order by dataid desc) A 
ORDER BY DataID

4.分页方案四：(利用sql的游标存储过程分页)

代码如下:

 
create procedure sqlPager 
@sql nvarchar(8000),--查询字符串 
@curpage int,--第N页 
@pagesize int --每页行数 
as 
set nocount on 
declare @P int,--P是游标的id 
@rowcount int 
exec sp_cursoropen @P output,@sql,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output 
select ceiling(1.0*@rowcount/@pagesize) as 总页数,@rowcount as 总行数,@curpage as 当前页 
set @curpage=(@curpage-1)*@pagesize+1 
exec sp_cursorfetch @P,16,@curpage,@pagesize 
exec sp_cursorclose @P 
set nocount off


方法整理如下：
　　代码基于pubs样板数据库
　　在sql中,一般就这两种方法.
　　1.使用临时表
　　可以使用select into 创建临时表,在第一列,加入Identify(int,1)作为行号,
　　这样在产生的临时表中,结果集就有了行号.也是目前效率最高的方法.
　　这种方法不能用于视图

代码如下:

 
　　set nocount on 
　　select IDentify(int,1) 'RowOrder',au_lname,au_fname into #tmp from authors 
　　select * frm #tmp 
　　drop table #tmp

　　2.使用自连接
　　不用临时表,在sql语句中,动态的进行排序.这种方法用到的连接是自连接,连接关系一般是
　　大于,

代码如下:

 
　　select rank=count(*),a1.au_lname,a1.au_fname 
　　from authors a1 inner join authors a2 on a1.au_lname + a1.au_fname >= a2.au_lname + a2.au_fname 
　　group by a1.au_lname,a1.au_fname 
　　order by count(*)

　　运行结果:
　　rank au_lname au_fname
　　----------- ---------------------------------------- --------------------
　　1 Bennet Abraham
　　2 Blotchet-Halls Reginald
　　3 Carson Cheryl
　　4 DeFrance Michel
　　5 del Castillo Innes
　　6 Dull Ann
　　7 Greene morningstar
　　... ....
缺点:
　　1.使用自联接，所以该方法不适用于处理大量行。它适用于处理几百行。
　　对于大型表，一定要使用索引以避免进行大范围的搜索,或用第一种方法.
　　2.不能正常处理重复值。当比较重复值时，会出现不连续的行编号。
　　如果不希望出现这种现象，可以在电子表格中插入结果时隐藏排序列，而是使用电子表格编号。
　　或用第一种方法
　　优点:
　　这些查询可以用于视图和结果格式设置中
　　在结果集中插入了行号，现在就可以将结果集合缓存起来，然后使用DataView，加入过滤条件
　　RowNum>PageIndex*PageSize And RowNum<=(PageIndex+1)*PageSize
　　就能实现快速的分页，而且不论你的页面数据绑定控件是什么（DataList,DataGrid,还是Repeate都可以）。
　　如果你使用的是DataGrid，那么建议不要使用这种技术。因为DataGrid的分页效率和它差不多。

关于SQL Server 得到行号的SQL和sqlserver获取行号的介绍现已完结，谢谢您的耐心阅读，如果想了解更多关于DataGear 变更部署数据库为SQL Server填坑指南（含转写后的SQL server代码及SQL server配置文件）、Kubernetes SQL Server或Azure SQL Server PaaS Azure SQL数据库容器化的SQL Server、SQL Server - SQL Server 2019 启用SQL Server验证Super Administrator(sa)用户登录、SQL Server SQL行号排序和分页(SQL查询中插入行号 自定义分页的另类实现)的相关知识，请在本站寻找。

本文标签：

上一篇SQL Server Hosting Toolkit

下一篇sql 交集 差集 并集 笛卡尔积（sql数据查询如何实现集合的并交差运算）

相关文章