SqlServer 2005 T-SQL Query 学习笔记(4)

时间:2024-01-26 07:44:06 

比如,我要建立一个1,000,000行的数字表:

CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
DECLARE @max AS INT, @rc AS INT;
SET @max = 1000000;
SET @rc = 1;

INSERT INTO Nums VALUES(1);
WHILE @rc * 2 <= @max
BEGIN
INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums;
SET @rc = @rc * 2;
END

INSERT INTO dbo.Nums
SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max;

这种方式非常巧妙,它并不是一个一个的循环插入,而是一次插入很多行,{1},{2},{3,4},{5,6,7,8}。。。

为什么这样会快呢?

是因为它节省了跟比较其他可用解决方案进行比较和记录这些日志的时间。

然后,作者给了一个CTE的递归的解决方案:

DECLARE @n AS BIGINT;
SET @n = 1000000;

WITH Nums AS
(
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM Nums WHERE n < @n
)
SELECT n FROM Nums
OPTION(MAXRECURSION 0);--为了移除默认100的递归限制

有个更优的CTE的解决方案,就是先生成很多行,然后用ROW_NUMBER进行计算,再选择ROW_NUMBER这列的值就可以了。


DECLARE @n AS BIGINT;
SET @n = 1000000;

WITH Base AS
(
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM Base WHERE n < CEILING(SQRT(@n))
),
Expand AS
(
SELECT 1 AS c
FROM Base AS B1, Base AS B2
),
Nums AS
(
SELECT ROW_NUMBER() OVER(ORDER BY c) AS n
FROM Expand
)
SELECT n FROM Nums WHERE n <= @n
OPTION(MAXRECURSION 0);



利用笛卡尔积进行不断的累加,达到了22n行。

最后,作者给出了一个函数,用于生成这样的数字表:


CREATE FUNCTION dbo.fn_nums(@n AS BIGINT) RETURNS TABLE
AS
RETURN
WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)
SELECT n FROM Nums WHERE n <= @n;
GO
标签:SqlServer,T-SQL
0
投稿

猜你喜欢

  • python安装scipy的步骤解析

    2021-09-09 03:20:36
  • asp如何直接调用Excel数据?

    2010-06-28 18:26:00
  • Redis 的 GeoHash详解

    2024-01-22 12:32:43
  • python 制作一个gui界面的翻译工具

    2022-04-21 20:16:55
  • Centos7系统下Mysql主从同步配置方案

    2024-01-24 11:29:18
  • python将一组数分成每3个一组的实例

    2021-11-21 01:40:35
  • Opencv+Python实现图像运动模糊和高斯模糊的示例

    2022-08-06 12:25:19
  • php通过exif_read_data函数获取图片的exif信息

    2023-10-27 09:01:44
  • 单击按钮将内容复制到剪贴板

    2008-08-22 13:08:00
  • MySQL简化输入小技巧

    2024-01-13 15:49:33
  • python内存监控工具memory_profiler和guppy的用法详解

    2023-02-17 01:15:18
  • Python实现多行注释的另类方法

    2021-04-28 21:49:12
  • Python发送邮件实现基础解析

    2022-01-28 10:33:22
  • 理解HTTP消息头

    2008-12-10 14:06:00
  • JavaScript中变量、指针和引用功能与操作示例

    2024-04-17 10:07:16
  • HTML5实现留言和回复页面样式

    2024-04-18 10:32:20
  • Python线上环境使用日志的及配置文件

    2023-11-12 13:23:56
  • 基于Python中单例模式的几种实现方式及优化详解

    2022-10-24 14:20:45
  • Python如何实现远程方法调用

    2022-11-11 20:42:15
  • MySQL主从复制配置心跳功能介绍

    2024-01-23 19:52:34
  • asp之家 网络编程 m.aspxhome.com