--方法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)
Tsql生成序列、日期序列
最新推荐文章于 2024-05-28 17:00:09 发布