Tsql生成序列、日期序列

--方法1
SET NOCOUNT ON
USE tempdb
GO
IF OBJECT_ID('dbo.nums')IS NOT NULL
DROP TABLE dbo.nums;
GO
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 nums SELECT n+@rc FROM nums;
    SET @rc=@rc*2
END
--SELECT n+@rc FROM nums WHERE n+@rc<=@max

GO
DECLARE @s AS DATETIME,@e AS DATETIME
SET @s='20140501'
SET @e='20141231'
SELECT @s+n-1 AS dt FROM nums WHERE n<=DATEDIFF(DAY,@s,@e)+1
SELECT * FROM nums  WHERE n<10
GO
--方法2
IF object_id('fn_nums') IS NOT NULL
BEGIN 
    PRINT 'Dropping function fn_nums'
    DROP FUNCTION fn_nums
    IF @@ERROR = 0 PRINT 'Function fn_nums dropped'
END
go

CREATE FUNCTION 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
DECLARE @s AS DATETIME,@e AS DATETIME
SET @s='20140501'
SET @e='20141231'
SELECT @s+n-1 AS dt FROM dbo.fn_nums(DATEDIFF(DAY,@s,@e))AS nums;
SELECT * FROM  dbo.fn_nums(10)

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值