SQL Server 实现数字辅助表实例代码

数字辅助表是一个连续整数的数列,通常用来实现多种不同的查询任务。大多分两类:足够大物理数字表和表函数,前者可以称为静态的,后者可以称为动态且按需生产。

物理数字表

物理数字表通常存在一个物理表,表记录相对足够大,相关的T-SQL代码如下:

IF OBJECT_ID(N'dbo.Nums', 'U') IS NOT NULL
BEGIN
  DROP TABLE dbo.Nums;
END
GO

CREATE TABLE dbo.Nums
(
  Num INT NOT NULL,
  CONSTRAINT PK_U_CL_Nums_Num PRIMARY KEY CLUSTERED
  (
    Num ASC
  )
);
GO

INSERT INTO dbo.Nums (Num)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum
FROM master.dbo.spt_values;
GO

注意:如何填充物理数字表的方法很多,为了演示作用使用了一种。

测试的T-SQL代码如下:

1 SELECT Num
2 FROM dbo.Nums;
3 GO

执行后的查询结果如下:

表函数

表函数实现使用交叉连接和CTE,SQL Server 2005和以上版本的T-SQL代码如下:

IF OBJECT_ID(N'dbo.ufn_GetNums', N'IF') IS NOT NULL
BEGIN
  DROP TABLE dbo.ufn_GetNums;
END
GO

--==================================
-- 功能: 获取指定范围的数字数列
-- 说明: 交叉最后层级的CTE得到的数据行:在L级(从0开始计数)得到的行的总数为2^2^L。
--    例如:在5级就会得到4 294 967 596行。5级的CTE提供了超过40亿的行。
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
--==================================
CREATE FUNCTION dbo.ufn_GetNums
(
  @bintLow BIGINT,
  @bintHigh BIGINT
) RETURNS TABLE
AS
RETURN
  WITH
    L0 AS (SELECT c FROM (VALUES(1), (1)) AS LO(c)),
    L1 AS (SELECT 1 AS c FROM L0 AS T CROSS JOIN L0 AS T2),
    L2 AS (SELECT 1 AS c FROM L1 AS T CROSS JOIN L1 AS T2),
    L3 AS (SELECT 1 AS c FROM L2 AS T CROSS JOIN L2 AS T2),
    L4 AS (SELECT 1 AS c FROM L3 AS T CROSS JOIN L3 AS T2),
    L5 AS (SELECT 1 AS c FROM L4 AS T CROSS JOIN L4 AS T2),
    Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum FROM L5)

  SELECT TOP (@bintHigh - @bintLow + 1) @bintLow + RowNum - 1 AS Num
  FROM Nums
  ORDER BY RowNum ASC;
GO

SQL Server 2012增加了有关分页的新特性,相关的T-SQL代码如下:

IF OBJECT_ID(N'dbo.ufn_GetNums2', N'IF') IS NOT NULL
BEGIN
  DROP TABLE dbo.ufn_GetNums2;
END
GO

--==================================
-- 功能: 获取指定范围的数字数列
-- 说明: 交叉最后层级的CTE得到的数据行:在L级(从0开始计数)得到的行的总数为2^2^L。
--    例如:在5级就会得到4 294 967 596行。5级的CTE提供了超过40亿的行。
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
--==================================
CREATE FUNCTION dbo.ufn_GetNums2
(
  @bintLow BIGINT,
  @bintHigh BIGINT
) RETURNS TABLE
AS
RETURN
  WITH
    L0 AS (SELECT c FROM (VALUES(1), (1)) AS LO(c)),
    L1 AS (SELECT 1 AS c FROM L0 AS T CROSS JOIN L0 AS T2),
    L2 AS (SELECT 1 AS c FROM L1 AS T CROSS JOIN L1 AS T2),
    L3 AS (SELECT 1 AS c FROM L2 AS T CROSS JOIN L2 AS T2),
    L4 AS (SELECT 1 AS c FROM L3 AS T CROSS JOIN L3 AS T2),
    L5 AS (SELECT 1 AS c FROM L4 AS T CROSS JOIN L4 AS T2),
    Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum FROM L5)

  SELECT @bintLow + RowNum - 1 AS Num
  FROM Nums
  ORDER BY RowNum ASC
  OFFSET 0 ROWS FETCH FIRST @bintHigh - @bintLow + 1 ROWS ONLY;
GO

以函数ufn_GetNums为例,演示相关的效果。获取指定范围的数字序列的T-SQL代码如下:

SELECT Num
FROM dbo.ufn_GetNums(11, 20);
GO

执行后的查询结果如下:

博友如有其他更好的解决方案,也请不吝赐教,万分感谢。

参考清单列表

1、《Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions》 作者 Itzik Ben-Gan(美国)(SQL Server Inside 有关书籍的作者)

感谢阅读,希望能帮助到大家,谢谢大家对本站的支持!

(0)

相关推荐

  • SqlServer存储过程实现及拼接sql的注意点

    这里我昨天碰到的问题就是执行一段根据变量tableName对不同的表进行字段状态的更改.由于服务器原因,我不能直接在数据访问层写SQL,所以只好抽离出来放到存储过程里面. 这里就出现了一个问题,我花费了好久才弄通! 其实就是很简单的一个SQL语句: update table1 set field1=value1,field2 = value2 where id = id 我写成什么样子了呢?大家且看: declare @tableName nvarchar(50), @field1 int, @

  • SQLServer批量更新两个关联表数据的方法

    本文实例讲述了SQLServer批量更新两个关联表数据的方法.分享给大家供大家参考,具体如下: 方式1: UPDATE a SET WtNo=b.NO from WT_Task a INNER JOIN WT_BasicInformation b ON a.WtId=b.ID; 方式2: UPDATE a SET a.WtNo=b.NO FROM WT_Task a,WT_BasicInformation b WHERE a.WtId=b.ID; 希望本文所述对大家SQL Server数据库程序

  • SqlServer2008误操作数据(delete或者update)后恢复数据的方法

    实际工作中,有时会直接在数据库中操作数据,比如对数据进行delete或者update操作,当进行这些操作的时候,如果没有加上 where条件或者where条件不合理,那么导致的结果可想而知,如果操作的又是线上数据库,那么这个后果将会非常严重. 当事情发生后,我们要想办法补救,针对于sqlserver2005数据库,有个很出名的工具Log Exploer.具体操作使用大家可以自行搜索;针对于sqlserver2008也有这样的工具,但是大多是需要付费的...我们尝试用 sqlserver的事务日志

  • 获取SqlServer存储过程定义的三种方法

    存储过程的概念 存储过程Procedure是一组为了完成特定功能的SQL语句集合,经编译后存储在数据库中,用户通过指定存储过程的名称并给出参数来执行. 存储过程中可以包含逻辑控制语句和数据操纵语句,它可以接受参数.输出参数.返回单个或多个结果集以及返回值. 由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中,所以存储过程运行要比单个的SQL语句块要快.同时由于在调用时只需用提供存储过程名和必要的参数信息,所以在一定程度上也可以减少网络流量.简单网络负担. 存储过程的优点 A. 存储

  • C#访问SQLServer增删改查代码实例

    一个专门实现访问sql server数据库增删改查的操作代码,分享给大家,具体内容如下 using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Data; usi

  • sqlserver进程死锁关闭的方法

    1.首先我们需要判断是哪个用户锁住了哪张表. --查询被锁表 select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName from sys.dm_tran_locks where resource_type='OBJECT' 查询后会返回一个包含spid和tableName列的表. 其中spid是进程名,tableName是表名. 2.了解到了究竟是哪个进程锁了哪张表后,需要通过进程找到锁

  • win2008 r2 服务器php+mysql+sqlserver2008运行环境配置(从安装、优化、安全等)

    win2008 r2 安装 http://www.jb51.net/article/38048.htm iis的安装 http://www.jb51.net/article/86390.htm php的安装注意事项: 下载非安全线程 版本 nts php 5.2.17 http://www.jb51.net/softs/268745.html 其它版本可以到 http://museum.php.net/php5/里面很多经典的老版本. PHP 5.5 (5.5.36) VC11 x64 Non

  • SqlServer查询和Kill进程死锁的语句

    查询死锁进程语句 select request_session_id spid, OBJECT_NAME(resource_associated_entity_id) tableName from sys.dm_tran_locks where resource_type='OBJECT' 杀死死锁进程语句 kill spid 下面再给大家分享一段关于sqlserver检测死锁;杀死锁和进程;查看锁信息 --检测死锁 --如果发生死锁了,我们怎么去检测具体发生死锁的是哪条SQL语句或存储过程?

  • Windows2012配置SQLServer2014AlwaysOn的图解

    SQLserver 2014 AlwaysOn增强了原有的数据库镜像功能,使得先前的单一数据库故障转移变成以组(多个数据)为单位的故障转移.同时可以支持多达9个复制伙伴,可读性辅助副本服务器等多个特性.对于以组为单位的数据库,主要是解决应用对于多个数据库之间存在的依赖性,从而进行整体转移.其次,可以将那些报表或者只读需求转移到只读辅助副本,从而大大减少主副本的负载,使得主副本更加容易扩展,更好地支持生产负载,以及对请求提供更快的响应. 本文描述了基于虚拟环境Windows 2012 + SQLs

  • SQL Server 实现数字辅助表实例代码

    数字辅助表是一个连续整数的数列,通常用来实现多种不同的查询任务.大多分两类:足够大物理数字表和表函数,前者可以称为静态的,后者可以称为动态且按需生产. 物理数字表 物理数字表通常存在一个物理表,表记录相对足够大,相关的T-SQL代码如下: IF OBJECT_ID(N'dbo.Nums', 'U') IS NOT NULL BEGIN DROP TABLE dbo.Nums; END GO CREATE TABLE dbo.Nums ( Num INT NOT NULL, CONSTRAINT

  • SQL Server 远程更新目标表数据的存储过程

    本文给大家分享一个远程更新目标库数据的存储过程,适用于更新列名一致,主键为Int类型,可远程链接的数据库. USE [Table]--切换到源表,就是数据最新的那个表 GO /****** Object: StoredProcedure [dbo].[proc_DataUpdate] Script Date: 2018/5/4 15:08:56 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ================

  • SQL Server 2008 数据库镜像部署实例之三 配置见证服务器

    前面已经完成了镜像数据库的配置,并进行那个了故障转移测试.接下来将部署见证服务器,实现自动故障转移. 一.关于见证服务器 1.若要支持自动故障转移,必须在高安全性模式下配置数据库镜像会话,并且还要具有第三个服务器实例(也称为"见证服务器").见证服务器是 SQL Server 的可选实例,它能使高安全性模式会话中的镜像服务器识别出是否要启动自动故障转移.与这两个伙伴不同的是,见证服务器并不能用于数据库.见证服务器的唯一角色是支持自动故障转移. 2.为了给数据库设置见证服务器,数据库所有

  • Angular.js 实现数字转换汉字实例代码

    AngularJS 简介 AngularJS 是一个 JavaScript 框架.它可通过 <script> 标签添加到 HTML 页面. AngularJS 通过 指令 扩展了 HTML,且通过 表达式 绑定数据到 HTML. 下面通过本文给大家介绍Angular.js 实现数字转换汉字实例代码,具体代码如下所示: // 1.实现输入数字输出对应汉字,要求使用angularjs,不准使用$watch函数,for循环:提示:ng-change指令 <div ng-app="my

  • SQL Server 数据库分区分表(水平分表)详细步骤

    1. 需求说明 将数据库Demo中的表按照日期字段进行水平分区分表.要求数据文件按一年一个文件存储,且分区的分割点会根据时间的增长自动添加(例如现在是2017年1月1日,将其作为一个分割点,即将2017年1月1日之前的数据存储到数据文件A中,将2017年1月1日的之后的数据存储到数据文件B中:当时间到2018年1月1日时,自动将2018年1月1日添加为一个新的分区分割点,并将2017年1月1日至2018年1月1日的数据存储在数据文件B中,将2018年1月1日之后的数据存储在一个新的数据文件C中,

  • javascript数字验证的实例代码(推荐)

    现在有一个需求如下图: 产品经理说Card Number只能让输入数字(中间的空格是格式自加的,也是用js实现的),有时候我脑海中出现了个声音,啥玩意,加个type=number不就行了,事实发现图样图森破了,先不说type=number后面会有个上下标(虽然用css可干掉),但是这个类型是支持科学输入法的,就是小数点和e这样的是可以输入的,于是乎只能用其他的方式了,后来想用检索到输入了非数字就干掉,但是这样还是能输入,想法被打回,于是乎最终采用了键盘输入控制的办法,其实很简单, 代码如下: v

  • JavaScript修改注册表实例代码

    注册表有关安全设置项的说明: 注册表路径: HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings\Zones 1.         Zones 项包含表示为计算机定义的每个安全区域的项.默认情况下,定义以下 5个区域(编号从 0  到 4): 值                   设置 ------------------------------ 0                 我的电脑

  • C语言动态顺序表实例代码

    目录 顺序表概念: 一.准备工作 二.顺序表的基本操作  1.顺序表的初始化函数 2.尾插函数(在尾部插入数据) 3.头插函数(在数组头部插入数据)  4.尾删函数 5.头删函数 6.在第pos的位置插入数据 7.删除第pos个位置的数据 8.修改第pos个位置的数据 9.查找函数. 10.销毁函数 11.打印函数 三.总代码: 顺序表概念:         顺序表是用一段物理地址连续的存储单元依次存储数据元素的线性结构.一般情况下用数组存储.在数组上完成数据的增删查改. 代码解析: 一.准备工

  • PowerShell连接SQL SERVER数据库进行操作的实现代码

    核心代码 #配置信息 $Database = 'DemoDB' $Server = '"WIN-AHAU9NO5R6U\DOG"' $UserName = 'kk' $Password = '123456' #创建连接对象 $SqlConn = New-Object System.Data.SqlClient.SqlConnection #使用账号连接MSSQL $SqlConn.ConnectionString = "Data Source=$Server;Initial

  • SQL Server中修改“用户自定义表类型”问题的分析与方法

    前言 SQL Server开发过程中,为了传入数据集类型的变量(比如接受C#中的DataTable类型变量),需要定义"用户自定义表类型",通过"用户自定义表类型"可以接收二维数据集作为参数,在需要修改"用户自定义表类型"的时候,增加字段,删除字段,修改字段类型等,它没有像表一样的alter table语法来进行修改. 只能通过删除重建来实现,但是在删除"用户自定义表类型"的时候会提示有对象引用它(某些存储过程用到了这个&qu

随机推荐