SQL2005生成序号(行号)表函数

IF OBJECT_ID('dbo.fn_nums') IS NOT NULL

    DROP FUNCTION dbo.fn_nums

GO

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

--调用函数

SELECT * FROM fn_nums(100)