【SQL】分享表值函数FMakeRows，用于生成行

时间：2022-09-07 09:29:28

------------更新：201501071730------------

评论中又有一位【笑东风】兄给出改善建议，在此先感谢他。原理是借助行数较多的一个系统视图sys.all_columns与自身做cross join，以得到大量现成行数，详情请见回复。在我的原文中我也提到考虑过这种借助现有系统对象得到行的方法，但我想当然认为这样会导致访问基础表，性能不会好，所以试都没试就pass了，但事实证明我错了，他的法子经测性能比倍增法好太多，再次自我教训，实践才是硬道理！！！再次感谢【笑东风】兄。最终实现如下：

/*----------------------

函数：生成行 0.01

Author:AhDung

Update:201412310925

----------------------*/

CREATE FUNCTION dbo.FMakeRows(@num INT)

RETURNS TABLE

RETURN (

SELECT TOP (CASE WHEN @num IS NULL OR @num<0 THEN 0 ELSE @num END) ROW_NUMBER() OVER(ORDER BY a.object_id) AS 'RowNo'

FROM sys.all_columns a CROSS JOIN sys.all_columns b

)

------------更新：201501061241------------

评论中有朋友提到不如用with，我理解他说的是CTE递归，在此先感谢一下这位【空紫竹】兄提出建议。遂试了下用CTE实现，代码如下：

CREATE FUNCTION dbo.FMakeRows2(@num INT)

RETURNS TABLE

RETURN (

WITH cte AS (

SELECT 1 AS 'RowNo',1 AS 'Lv'

UNION ALL

SELECT RowNo+Lv,Lv*2 FROM cte WHERE RowNo+Lv<=@num

UNION ALL

SELECT RowNo+Lv*2,Lv*2 FROM cte WHERE RowNo+Lv*2<=@num

)

SELECT RowNo FROM cte

)

功能一样，原理是递归倍增，语句变少了，但性能比不上原文的方法，16384行上述方法要400ms左右，与逐行法差不多，而原文方法只要140ms，所以算不上好方法，权当学习一下CTE递归知识。如果我的实现有问题，还望路过大侠指点，谢谢。

------------原文：201412311300------------

作用：传入整数x，返回一张x行的表，只有一列RowNo，存储各行序号。

对于这个需求，我先是找有没有现成的函数或过程，结果是没找到，如果路过的朋友知道，还望告知，谢谢。

使用示例：

【SQL】分享表值函数FMakeRows，用于生成行

至于该函数具体可以应用到哪些场景，只可意会，需要的人自然觉得有用，觉得没用的说明不需要。上代码：

/*----------------------

函数：生成行 0.01

Author:AhDung

Update:201412310925

----------------------*/

ALTER FUNCTION dbo.FMakeRows(@num INT)

RETURNS @t TABLE (RowNo INT)

BEGIN

IF @num IS NULL OR @num <= 0 RETURN

INSERT @t VALUES(1)

DECLARE @no INT = 1

WHILE @no*2 <= @num

BEGIN

INSERT @t SELECT RowNo+@no FROM @t

SET @no *= 2

END

INSERT @t SELECT TOP (@num-@no) RowNo+@no FROM @t

RETURN

END

实现说明：原理是先给@t塞一个初始行，完了循环insert自身，如此1变2、2变4、4变万物……，每一圈后@t的行数都是上一圈的2倍，直到行数x2大于所需行数（@num）前打住，即要把行数控制在小于等于@num的范围内，最后从现有行中抽取一部分补齐所差的行。例如，需要的行数是13，转到3圈后，@t有8行，就要打住了，因为再转就成16行了，8距离13所差的5行最后通过从@t中抽取top 5补齐。

实现该函数一开始想到的是根据@num循环，每圈插一行，需要几行就转几圈（逐行法），逻辑很简单，但这样做很老实，事实证明效率也不如上述方法（行数倍增），两种方法经测试到500行时就有明显差异了，到16384行时，倍增法在140ms左右，逐行法400ms左右，我想原因就是倍增法大大减少了循环圈数，16384行只需转14圈，而逐行法要老老实实转足16384圈呐~怕怕。

也想过从某个必定存在的系统表/视图获取行，如sys.objects，但这样会访问基础表，即使你根本不select它的任何字段，这样性能必然不如纯内存操作来的好，试都不用试。再说也不地道，作为函数，依赖越少越健壮。

路过朋友如有更好方法，还请不吝赐教，非常感谢。

【SQL】分享表值函数FMakeRows，用于生成行的更多相关文章

  1. SQL Server 表值函数

    表值函数还是很有意思的,我现在想实现一个功能.就是我查询出来的内容,我要对结果进行再次查询,也要输入参数,我想了想 1.存储过程  不能对执行后的结果进行再次查询,需要创建临时表,太麻烦 2.视图  ...

  2. 【SQL】sql版Split函数。用于拆分字符串为单列表格

    功能与.net版string.Split函数类似,只不过.net返回的是数组,这个返回的是一个单列表格,每个拆分出来的子串占一行.可选是否移除空格子串和重复项.市面上类似的函数不算少,但大多都是在循环 ...

  3. PCB MS SQL表值函数与CLR 表值函数 &lpar;例：字符串分割转表&rpar;

    将字符串分割为表表经常用到,这里 SQL表值函数与CLR  表值函数,两种实现方法例出来如下: SELECT * FROM FP_EMSDB_PUB.dbo.SqlSplit('/','1oz/1.5 ...

  4. T-SQL编程 —— 用户自定义函数&lpar;内嵌表值函数&rpar;

    内嵌表值函数 接上 <T-SQL编程 -- 用户自定义函数(标量函数)> http://www.cnblogs.com/viusuangio/p/6212072.html 内嵌表值函数可以 ...

  5. sql 表值函数与标量值函数

    写sql存储过程经常需要调用一些函数来使处理过程更加合理,也可以使函数复用性更强,不过在写sql函数的时候可能会发现,有些函数是在表值函数下写的有些是在标量值下写的,区别是表值函数只能返回一个表,标量 ...

  6. SQL Server中使用表值函数

    函数有很多限制,不能使用动态语句,不能使用临时表等等...细看一下,直接写语句就行了,不用动态语句 insert into @re select id,parid,@I from videoclass ...

  7. sql server 中的表值函数和标量值函数

      顾名思义:表值函数返回的是表,而标量值函数可以返回基类型 一.表值函数 用户定义表值函数返回 table 数据类型.对于内联表值函数,没有函数主体:表是单个 SELECT 语句的结果集. 以下示例 ...

  8. SQL Server进阶（六）表表达式--派生表、公用表表达式&lpar;CTE&rpar;、视图和内联表值函数

    概述 表表达式是一种命名的查询表达式,代表一个有效地关系表.可以像其他表一样,在数据处理中使用表表达式. SQL Server支持四种类型的表表达式:派生表,公用表表达式,视图和内联表值函数. 为什么 ...

  9. PCB MS SQL 标量函数与表值函数&lpar;CLR&rpar; 实现文件与目录操作

    一.C#写SQL SERVER(CLR)实现文件操作 标量函数: 文件移动 ,复制,检测文件存在,写入新文件文本,读取文本,创建目录,删除目录,检测目录是否存在 /// <summary> ...

随机推荐

  1. pcl曲面重建模块-贪婪三角形投影算法实例

    贪婪三角形投影算法 在pcl-1.8测试 #include <pcl/point_types.h> #include <pcl/io/pcd_io.h> #include &l ...

  2. python函数基础 与文件操作

    函数的定义 函数是通过赋值传递的,参数通过赋值传递给函数.def语句将创建一个函数对象并将其赋值给一个变量名,def语句的一般格式如下: def function_name(arg1,arg2[,.. ...

  3. &num;mysql&colon;command not found

    一.问题描述 1.在linux中已经安装好mysql,通过#ps -ef |grep mysql 能显示mysql已经启动,但去进入mysql命令页面出现如下问题: [root@root ~]# my ...

  4. x-editable 的使用方法

    1.首先在html网页中定义一个a标签(如下<%#%>是asp.net的语法) <a href="#"  data-pk="<%#Eval(&qu ...

  5. DJANGO中filter&lowbar;horizontal和raw&lowbar;id&lowbar;fields的作用

    在作多项选择的操作方便性,及单项选择太多时,会有好一点的体验... class UserAuthorAdmin(admin.ModelAdmin): # raw_id_fields = ('group ...

  6. 关于jQuery中的attr和data问题

    今天在使用data获取属性并且赋值时遇到一个小问题,写下来防止以后再跳坑. 在使用jQuery获取自定义属性值时,我们习惯用 $(selector).attr('data-value'); jQuer ...

  7. Pdf File Writer 中文应用（PDF文件编写器C＃类库）

    该文由小居工作室(QQ:2482052910)    翻译并提供解答支持,原文地址:Pdf File Writer 中文应用(PDF文件编写器C#类库):http://www.cnblogs.com/ ...

  8. Android OpenGL ES 开发（六）&colon; OpenGL ES 添加运动效果

    在屏幕上绘制图形只是OpenGL的相当基础的特点,你也可以用其他的Android图形框架类来实现这些,包括Canvas和Drawable对象.OpenGL ES为在三维空间中移动和变换提供了额外的功能 ...

  9. poj--2299（树状数组&plus;离散化）

    一.离散化: https://www.cnblogs.com/2018zxy/p/10104393.html 二.逆序数 AC代码: #include<iostream> #include ...

  10. 假如想要建设一个能承受500万PV&sol;每天的网站，服务器每秒要处理多少个请求才能应对？

    假如想要建设一个能承受500万PV/每天的网站,服务器每秒要处理多少个请求才能应对?如何计算? 1.PV是什么:PV是page view的简写.PV是指页面的访问次数,每打开或刷新一次页面,就算做一个 ...

相关文章