时间戳

首页 > 解决方案 > 消除递归 CTE

sql-server - 消除递归 CTE

问题描述

每个人，

我希望有人可以帮助我获得相同的结果并消除这种递归 CTE。原则上听起来很简单，但我已经为此工作了至少几天，但什么也想不出来。最后一件事，我知道 OPTION(MAXRECURSION 0) 选项。这对我们来说不是一个可行的选择。我们处于生产环境中，不希望数据库爬行。如果可能，我们希望摆脱递归 CTE。

WITH cte AS
(
    SELECT DATEADD(month, DATEDIFF(month, 0, @StartDate), 0) AS StartOfMonth, 
               DATEADD(s, 0, DATEADD(mm, DATEDIFF(m, 0, @StartDate) + 1, 0)) AS EndOfMonth
        UNION ALL
        SELECT DATEADD(month, 1, StartOfMonth) AS StartOfMonth, 
               DATEADD(s, 0, DATEADD(mm, DATEDIFF(m, 0, DATEADD(month, 1, StartOfMonth)) + 1, 0)) AS EndOfMonth  
        FROM   cte
        WHERE  DATEADD(month, 1, StartOfMonth) <= @EndDate
)

上面的代码驻留在 SQL Server 中名为 fn_SplitPeriodIntoMonths 的表值函数中。

可以调用函数

SELECT * FROM fn_SplitPeriodIntoMonths('2018-01-01','2018-09-01')

然后结果是这样的

StartOfMonth                   EndOfMonth
2018-01-01 00:00:00.000        2018-02-01 00:00:00.000
2018-02-01 00:00:00.000        2018-03-01 00:00:00.000
2018-03-01 00:00:00.000        2018-04-01 00:00:00.000
2018-04-01 00:00:00.000        2018-05-01 00:00:00.000
2018-05-01 00:00:00.000        2018-06-01 00:00:00.000
2018-06-01 00:00:00.000        2018-07-01 00:00:00.000
2018-07-01 00:00:00.000        2018-08-01 00:00:00.000
2018-08-01 00:00:00.000        2018-09-01 00:00:00.000
2018-09-01 00:00:00.000        2018-10-01 00:00:00.000

如您所见，结果是按月对期间进行细分。比较酷。然后可以以多种方式使用它。但是，如果要使用具有“太多”行的选择来调用它，我们会达到 MAXRECUSION LIMIT。

任何帮助将不胜感激。

祝一切顺利，

乔治

标签： sql-serveruser-defined-functionscommon-table-expressionrecursive-query

解决方案

/****** Object:  UserDefinedFunction [dbo].[fn_SplitPeriodIntoMonths]    Script Date: 24/07/2018 17:29:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Andy Deighton
-- Create date: 24/07/2018
-- Description: Return a date table
-- =============================================
ALTER FUNCTION [dbo].[fn_SplitPeriodIntoMonths]
(   
    -- Add the parameters for the function here
    @START DATE,
    @END DATE
)
RETURNS TABLE 
AS
RETURN 
(

    SELECT AM.StartOfMonth, EOMONTH(AM.StartOfMonth) EndOfMonth FROM
    (
    SELECT datefromparts(100 * (c.century - 1) + 10 * (d.decade -1) + y.[year],M.[month],1) StartOfMonth                   
                                  FROM 
                                  (SELECT 20 century UNION SELECT 21) C
                                    CROSS JOIN 
                                    (SELECT decade FROM 
                                                (VALUES (1), (2), (3), (4), (5), (6),(7),(8),(9), (10)) 
                                                    X(decade)) D
                                    CROSS JOIN 
                                    (SELECT [year] FROM 
                                                (VALUES (0), (1), (2), (3), (4), (5),(6),(7),(8), (9)) 
                                                    X([year])) Y
                                    CROSS JOIN 
                                    (SELECT [month] FROM 
                                                (VALUES (1), (2), (3), (4), (5),(6),(7),(8), (9), (10), (11),(12)) 
                                                    X([month])) M
    ) AM WHERE AM.StartOfMonth BETWEEN @START AND @END




)

样品调用

    select * from dbo.fn_SplitPeriodIntoMonths('19660101','20450101') order by startofmonth

推荐阅读