将递归查询与非递归查询组合

Question

我创建了两个查询-一个是递归的，一个不是-我想将它们组合成一个连贯的查询(使用left join将非递归查询连接到递归查询)-但我不确定如何去做。我试图将递归的一个封装到一个巨大的括号中，然而，这似乎不起作用。

递归查询

DECLARE     @UserId BIGINT = 31946,
    @StartDateOfWeek DATETIME = '8/4/2014',
    @EndDateOfWeek DATETIME = '8/10/2014'
;WITH
    Dates (D) AS 
    (
        SELECT 0 UNION ALL
        SELECT 1 + D FROM Dates WHERE D < DATEDIFF(DD, @StartDateOfWeek, @EndDateOfWeek)
    )
    SELECT
        CAST([Date] as DATE) [DayReport],
        SUM([TotalHours]) [TimeReport]
    FROM
    (
        SELECT
             DD2.[Date],
            0 [TotalHours]
        FROM
        (
            SELECT
                DATEADD(DD, D, @StartDateOfWeek) [Date]
            FROM
                Dates
        ) DD2
        UNION 
        SELECT
            [Date],
            SUM([RealMinutes])/60 [TotalHours]
        FROM
        (
            SELECT
                CAST(EventCreateDate AS DATE) [Date],
                CASE
                    WHEN [Minutes] >= 30 AND CAST([Minutes] AS FLOAT)/CAST([PageCount] AS FLOAT) >= 1 THEN 
                        CASE WHEN CAST([PageCount] AS FLOAT) * 0.5 > 45 THEN 45 ELSE CAST([PageCount] AS FLOAT) * 0.5 END 
                    WHEN [Minutes] >= 60 AND CAST([Minutes] AS FLOAT)/CAST([PageCount] AS FLOAT) < 1 THEN 60
                            ELSE
                        [Minutes] /*Half minute*/
                END [RealMinutes]
            FROM
        (
            SELECT
                CASE WHEN c.[PageCount] = 0 THEN 1 WHEN c.[PageCount] IS NULL THEN 1 ELSE c.[PageCount] END [PageCount],
                ev.EventCreateDate, 
                DATEDIFF(MI, ev.EventCreateDate, ev.EventCompletionDate) [MINUTES]
            FROM 
                tbl_Papers c, tbl_PaperEvents ev, tbl_Users u
            WHERE
                c.ChartId = ev.ChartId
                AND CAST(ev.EventCreateDate AS DATE) BETWEEN CAST(@StartDateOfWeek AS DATE) AND CAST(@EndDateOfWeek AS DATE)
                AND ev.EventCreateUserId = @UserId
                AND ev.EventId = 201 
                AND u.UserId = ev.EventCreateUserId
        ) TC
    ) TH
    GROUP BY
        [Date]
) TF
GROUP BY
    [Date]

Non-Recurisve查询

DECLARE
    @UserId BIGINT = 31946,
    @StartDateOfWeek DATETIME = '8/4/2014',
    @EndDateOfWeek DATETIME = '8/10/2014'
SELECT 
        CAST ([TimeEntryDate] as DATE) Time
        ,DATEDIFF(HH, [TimeEntryStartTime], [TimeEntryEndTime]) AS TimeEntryTotalHours
    FROM 
        [tbl_TimeEntries] te
        INNER JOIN tbl_Users u ON u.UserId = te.[TimeEntryUserId]
        INNER JOIN tbl_UserPermissions up ON up.UserId = u.UserId
        INNER JOIN tbl_Permissions p ON p.PermissionId = up.PermissionId
        LEFT OUTER JOIN tbl_ApprovalStatuses ast ON ast.ApprovalStatusId = te.TimeEntryStatusId 
    WHERE
        (@UserId = te.[TimeEntryUserId] OR @UserId = -1)
        AND p.PermissionType = 'Coder'
        AND((te.[TimeEntryStartTime] BETWEEN  @StartDateOfWeek AND @EndDateOfWeek) OR (@StartDateOfWeek IS NULL))

我希望看到的结果

DayReport /*From Recursive Query (which can be matched to the Non-Recursive Query)*/ | TimeReport /*From Recursive Query*/ | TimeEntryTotalHours /*From Non-Recursive Query*/
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
8/14/2014                                                                            | 1                                   | 2

Answer 1

就像这样组合它们:

DECLARE
    @UserId BIGINT = 31946,
    @StartDateOfWeek DATETIME = '8/4/2014',
    @EndDateOfWeek DATETIME = '8/10/2014'

;WITH
    Dates (D) AS 
    (
        SELECT 0 UNION ALL
        SELECT 1 + D FROM Dates WHERE D < DATEDIFF(DD, @StartDateOfWeek, @EndDateOfWeek)
    ),
    Times AS (
    SELECT
        CAST([Date] as DATE) [DayReport],
        SUM([TotalHours]) [TimeReport]
    FROM
    (
        SELECT
             DD2.[Date],
            0 [TotalHours]
        FROM
        (
            SELECT
                DATEADD(DD, D, @StartDateOfWeek) [Date]
            FROM
                Dates
        ) DD2
        UNION 
        SELECT
            [Date],
            SUM([RealMinutes])/60 [TotalHours]
        FROM
        (
            SELECT
                CAST(EventCreateDate AS DATE) [Date],
                CASE
                    WHEN [Minutes] >= 30 AND CAST([Minutes] AS FLOAT)/CAST([PageCount] AS FLOAT) >= 1 THEN 
                        CASE WHEN CAST([PageCount] AS FLOAT) * 0.5 > 45 THEN 45 ELSE CAST([PageCount] AS FLOAT) * 0.5 END 
                    WHEN [Minutes] >= 60 AND CAST([Minutes] AS FLOAT)/CAST([PageCount] AS FLOAT) < 1 THEN 60
                            ELSE
                        [Minutes] /*Half minute*/
                END [RealMinutes]
            FROM
        (
            SELECT
                CASE WHEN c.[PageCount] = 0 THEN 1 WHEN c.[PageCount] IS NULL THEN 1 ELSE c.[PageCount] END [PageCount],
                ev.EventCreateDate, 
                DATEDIFF(MI, ev.EventCreateDate, ev.EventCompletionDate) [MINUTES]
            FROM 
                tbl_Papers c, tbl_PaperEvents ev, tbl_Users u
            WHERE
                c.ChartId = ev.ChartId
                AND CAST(ev.EventCreateDate AS DATE) BETWEEN CAST(@StartDateOfWeek AS DATE) AND CAST(@EndDateOfWeek AS DATE)
                AND ev.EventCreateUserId = @UserId
                AND ev.EventId = 201 
                AND u.UserId = ev.EventCreateUserId
        ) TC
    ) TH
    GROUP BY
        [Date]
) TF
GROUP BY
    [Date]),
otherQuery AS ( SELECT
        CAST ([TimeEntryDate] as DATE) [Time]
        ,DATEDIFF(HH, [TimeEntryStartTime], [TimeEntryEndTime]) AS TimeEntryTotalHours
    FROM 
        [tbl_TimeEntries] te
        INNER JOIN tbl_Users u ON u.UserId = te.[TimeEntryUserId]
        INNER JOIN tbl_UserPermissions up ON up.UserId = u.UserId
        INNER JOIN tbl_Permissions p ON p.PermissionId = up.PermissionId
        LEFT OUTER JOIN tbl_ApprovalStatuses ast ON ast.ApprovalStatusId = te.TimeEntryStatusId 
    WHERE
        (@UserId = te.[TimeEntryUserId] OR @UserId = -1)
        AND p.PermissionType = 'Coder'
        AND((te.[TimeEntryStartTime] BETWEEN  @StartDateOfWeek AND @EndDateOfWeek) OR (@StartDateOfWeek IS NULL))
)
SELECT 
Times.DayReport,
Times.TimeReport,
OtherQuery.TimeEntryTotalHours
FROM
        Times LEFT JOIN OtherQuery ON Times.DayReport = OtherQuery.[Time]

基本上，您可以在WITH子句中添加更多的CTE，每个子句用逗号分隔