文章/答案/技术大牛

发布

问关于条款联盟的问题
EN

Database Administration用户

提问于 2014-10-23 17:40:55

回答 2查看 210关注 0票数 1

WITH [ProgressInvoicesPeriodCost] AS
(
    SELECT
        ProgressInvoices.ProjectId
        , ProgressInvoices.PeriodId
        , SUM(ProgressInvoiceDetails.TotalThisInvoice) AS TotalThisInvoice
    FROM
        CostManagement_ProgressInvoices AS ProgressInvoices
        LEFT JOIN CostManagement_ProgressInvoiceDetails AS ProgressInvoiceDetails
            ON  ProgressInvoices.Id = ProgressInvoiceDetails.ProgressInvoiceId
    WHERE
        ProgressInvoices.ProjectId IN (@Projectlisting)

    GROUP BY
        ProgressInvoices.ProjectId
        , ProgressInvoices.PeriodId
)

[CommitmentCOPeriodCost] AS
(
    SELECT
        CommitmentCOs.ProjectId
        , CommitmentCODetails.PeriodId
        , SUM(CommitmentCODetails.AmountApproved) AS AmountApproved
    FROM
        CostManagement_CommitmentCOs AS CommitmentCOs
        LEFT JOIN CostManagement_CommitmentCODetails AS CommitmentCODetails
            ON  CommitmentCOs.Id = CommitmentCODetails.CommitmentCOId
    WHERE
        CommitmentCOs.ProjectId IN (@Projectlisting) AND CommitmentCOs.PostAsId = 2

    GROUP BY
        CommitmentCOs.ProjectId
        , CommitmentCODetails.PeriodId
)

[CommitmentPeriodCost] AS
(
    SELECT
        Commitments.ProjectId
        , CommitmentDetails.PeriodId
        , SUM(CommitmentDetails.TotalCost) AS TotalCost

    FROM
        CostManagement_Commitments AS Commitments
        LEFT JOIN CostManagement_CommitmentDetails AS CommitmentDetails
            ON  Commitments.Id = CommitmentDetails.CommitmentId
    WHERE
        Commitments.ProjectId IN (@Projectlisting)

    GROUP BY
        Commitments.ProjectId
        , CommitmentDetails.PeriodId
)




SELECT
    ProjectId
    ,ProjectName
    ,'INV' as GroupRecordType
    ,'INV' as RecordType
    , PeriodId
    , Periods.Period
    , TotalThisInvoice AS TotalCost
    , (
            SELECT  SUM(TotalThisInvoice)
            FROM    ProgressInvoicesPeriodCost AS RunningCost
            WHERE   ProjectId = ProgressInvoicesPeriodCost.ProjectId
                    AND PeriodId <= ProgressInvoicesPeriodCost.PeriodId
    ) AS RunningTotal

FROM
    [ProgressInvoicesPeriodCost]
LEFT OUTER JOIN Periods
            ON  ProgressInvoicesPeriodCost.PeriodId = Periods.Id
LEFT OUTER JOIN Projects
            ON ProgressInvoicesPeriodCost.ProjectId = Projects.Id

Where (Period <= @ToPeriod) and ProgressInvoicesPeriodCost.ProjectId IN (@Projectlisting)AND 'INV' IN (@RecordTypes)


ORDER BY
    ProjectId
    , Period


UNION


SELECT
    ProjectId
    ,ProjectName
    ,'COM' as GroupRecordType
    ,'CO' as RecordType
    , PeriodId
    , Periods.Period
    , AmountApproved AS TotalCost
    , (
            SELECT  SUM(AmountApproved)
            FROM    CommitmentCOPeriodCost AS RunningCost
            WHERE   ProjectId = CommitmentCOPeriodCost.ProjectId
                    AND PeriodId <= CommitmentCOPeriodCost.PeriodId
    ) AS RunningTotal

FROM
    [CommitmentCOPeriodCost]
LEFT OUTER JOIN Periods
            ON  CommitmentCOPeriodCost.PeriodId = Periods.Id
LEFT OUTER JOIN Projects
            ON CommitmentCOPeriodCost.ProjectId = Projects.Id

Where (Period <= @ToPeriod) and CommitmentCOPeriodCost.ProjectId IN (@Projectlisting)AND 'CO' IN (@RecordTypes)


ORDER BY
    ProjectId
    , Period

UNION

SELECT
    ProjectId
    ,ProjectName
    ,'COM' as GroupRecordType
    ,'COM' as RecordType
    , PeriodId
    , Periods.Period
    , TotalCost
    , (
            SELECT  SUM(TotalCost)
            FROM    CommitmentPeriodCost AS RunningCost
            WHERE   ProjectId = CommitmentPeriodCost.ProjectId
                    AND PeriodId <= CommitmentPeriodCost.PeriodId
    ) AS RunningTotal

FROM
    [CommitmentPeriodCost]
LEFT OUTER JOIN Periods
            ON  CommitmentPeriodCost.PeriodId = Periods.Id
LEFT OUTER JOIN Projects
            ON CommitmentPeriodCost.ProjectId = Projects.Id

Where (Period <= @ToPeriod) and CommitmentPeriodCost.ProjectId IN (@Projectlisting)AND 'COM' IN (@RecordTypes)

ORDER BY
    ProjectId
    , Period

我需要帮助，试图将这3与AS部分。我得到以下错误：

好了！

仅使用1与的查询

sql-server

union

cte

回答 2

Database Administration用户

回答已采纳

发布于 2014-10-23 17:50:58

你有两个问题。首先，在CTE之间需要逗号。第二，对于一组UNIONed语句只能有一个ORDER。只需在最后点一份。

WITH [ProgressInvoicesPeriodCost] AS
(
    SELECT
        ProgressInvoices.ProjectId
        , ProgressInvoices.PeriodId
        , SUM(ProgressInvoiceDetails.TotalThisInvoice) AS TotalThisInvoice
    FROM
        CostManagement_ProgressInvoices AS ProgressInvoices
        LEFT JOIN CostManagement_ProgressInvoiceDetails AS ProgressInvoiceDetails
            ON  ProgressInvoices.Id = ProgressInvoiceDetails.ProgressInvoiceId
    WHERE
        ProgressInvoices.ProjectId IN (@Projectlisting)

    GROUP BY
        ProgressInvoices.ProjectId
        , ProgressInvoices.PeriodId
),

[CommitmentCOPeriodCost] AS
(
    SELECT
        CommitmentCOs.ProjectId
        , CommitmentCODetails.PeriodId
        , SUM(CommitmentCODetails.AmountApproved) AS AmountApproved
    FROM
        CostManagement_CommitmentCOs AS CommitmentCOs
        LEFT JOIN CostManagement_CommitmentCODetails AS CommitmentCODetails
            ON  CommitmentCOs.Id = CommitmentCODetails.CommitmentCOId
    WHERE
        CommitmentCOs.ProjectId IN (@Projectlisting) AND CommitmentCOs.PostAsId = 2

    GROUP BY
        CommitmentCOs.ProjectId
        , CommitmentCODetails.PeriodId
),

[CommitmentPeriodCost] AS
(
    SELECT
        Commitments.ProjectId
        , CommitmentDetails.PeriodId
        , SUM(CommitmentDetails.TotalCost) AS TotalCost

    FROM
        CostManagement_Commitments AS Commitments
        LEFT JOIN CostManagement_CommitmentDetails AS CommitmentDetails
            ON  Commitments.Id = CommitmentDetails.CommitmentId
    WHERE
        Commitments.ProjectId IN (@Projectlisting)

    GROUP BY
        Commitments.ProjectId
        , CommitmentDetails.PeriodId
)




SELECT
    ProjectId
    ,ProjectName
    ,'INV' as GroupRecordType
    ,'INV' as RecordType
    , PeriodId
    , Periods.Period
    , TotalThisInvoice AS TotalCost
    , (
            SELECT  SUM(TotalThisInvoice)
            FROM    ProgressInvoicesPeriodCost AS RunningCost
            WHERE   ProjectId = ProgressInvoicesPeriodCost.ProjectId
                    AND PeriodId <= ProgressInvoicesPeriodCost.PeriodId
    ) AS RunningTotal

FROM
    [ProgressInvoicesPeriodCost]
LEFT OUTER JOIN Periods
            ON  ProgressInvoicesPeriodCost.PeriodId = Periods.Id
LEFT OUTER JOIN Projects
            ON ProgressInvoicesPeriodCost.ProjectId = Projects.Id

Where (Period <= @ToPeriod) and ProgressInvoicesPeriodCost.ProjectId IN (@Projectlisting)AND 'INV' IN (@RecordTypes)



UNION


SELECT
    ProjectId
    ,ProjectName
    ,'COM' as GroupRecordType
    ,'CO' as RecordType
    , PeriodId
    , Periods.Period
    , AmountApproved AS TotalCost
    , (
            SELECT  SUM(AmountApproved)
            FROM    CommitmentCOPeriodCost AS RunningCost
            WHERE   ProjectId = CommitmentCOPeriodCost.ProjectId
                    AND PeriodId <= CommitmentCOPeriodCost.PeriodId
    ) AS RunningTotal

FROM
    [CommitmentCOPeriodCost]
LEFT OUTER JOIN Periods
            ON  CommitmentCOPeriodCost.PeriodId = Periods.Id
LEFT OUTER JOIN Projects
            ON CommitmentCOPeriodCost.ProjectId = Projects.Id

Where (Period <= @ToPeriod) and CommitmentCOPeriodCost.ProjectId IN (@Projectlisting)AND 'CO' IN (@RecordTypes)


UNION

SELECT
    ProjectId
    ,ProjectName
    ,'COM' as GroupRecordType
    ,'COM' as RecordType
    , PeriodId
    , Periods.Period
    , TotalCost
    , (
            SELECT  SUM(TotalCost)
            FROM    CommitmentPeriodCost AS RunningCost
            WHERE   ProjectId = CommitmentPeriodCost.ProjectId
                    AND PeriodId <= CommitmentPeriodCost.PeriodId
    ) AS RunningTotal

FROM
    [CommitmentPeriodCost]
LEFT OUTER JOIN Periods
            ON  CommitmentPeriodCost.PeriodId = Periods.Id
LEFT OUTER JOIN Projects
            ON CommitmentPeriodCost.ProjectId = Projects.Id

Where (Period <= @ToPeriod) and CommitmentPeriodCost.ProjectId IN (@Projectlisting)AND 'COM' IN (@RecordTypes)

ORDER BY
    ProjectId
    , Period

票数 3

Database Administration用户

发布于 2014-10-23 21:35:42

仅仅是关于嵌套CTE的一句话，每次引用时都会对它们进行评估。换句话说，它们的行为不像临时表，数据不是以某种特殊方式缓存的。因此，对于多次引用的CTE (即在UNION中引用一次，在RunningTotal子查询中一次)，可能会造成性能损失。我用您的查询设置了一个简单的平台，只有几百行来演示这一点。第一个调用(43,070读)是您的查询，接下来的四个调用是我使用临时表缓存CTE结果的重写，总共只注册了702个读取：

IF OBJECT_ID('tempdb..#ProgressInvoicesPeriodCost') IS NOT NULL DROP TABLE tempdb..#ProgressInvoicesPeriodCost
IF OBJECT_ID('tempdb..#CommitmentCOPeriodCost') IS NOT NULL DROP TABLE tempdb..#CommitmentCOPeriodCost
IF OBJECT_ID('tempdb..#CommitmentPeriodCost') IS NOT NULL DROP TABLE tempdb..#CommitmentPeriodCost

;WITH [ProgressInvoicesPeriodCost] AS
(
    SELECT
        ProgressInvoices.ProjectId
        , ProgressInvoices.PeriodId
        , SUM(ProgressInvoiceDetails.TotalThisInvoice) AS TotalThisInvoice
    FROM
        CostManagement_ProgressInvoices AS ProgressInvoices
        LEFT JOIN CostManagement_ProgressInvoiceDetails AS ProgressInvoiceDetails
            ON  ProgressInvoices.Id = ProgressInvoiceDetails.ProgressInvoiceId
    WHERE
        ProgressInvoices.ProjectId = @Projectlisting

    GROUP BY
        ProgressInvoices.ProjectId
        , ProgressInvoices.PeriodId
)
SELECT *
INTO #ProgressInvoicesPeriodCost
FROM [ProgressInvoicesPeriodCost]

;WITH [CommitmentCOPeriodCost] AS
(
    SELECT
        CommitmentCOs.ProjectId
        , CommitmentCODetails.PeriodId
        , SUM(CommitmentCODetails.AmountApproved) AS AmountApproved
    FROM
        CostManagement_CommitmentCOs AS CommitmentCOs
        LEFT JOIN CostManagement_CommitmentCODetails AS CommitmentCODetails
            ON  CommitmentCOs.Id = CommitmentCODetails.CommitmentCOId
    WHERE
        CommitmentCOs.ProjectId = @Projectlisting 
        AND CommitmentCOs.PostAsId = 2

    GROUP BY
        CommitmentCOs.ProjectId
        , CommitmentCODetails.PeriodId
)
SELECT *
INTO #CommitmentCOPeriodCost
FROM [CommitmentCOPeriodCost]

;WITH [CommitmentPeriodCost] AS
(
    SELECT
        Commitments.ProjectId
        , CommitmentDetails.PeriodId
        , SUM(CommitmentDetails.TotalCost) AS TotalCost

    FROM
        CostManagement_Commitments AS Commitments
        LEFT JOIN CostManagement_CommitmentDetails AS CommitmentDetails
            ON  Commitments.Id = CommitmentDetails.CommitmentId
    WHERE
        Commitments.ProjectId = @Projectlisting

    GROUP BY
        Commitments.ProjectId
        , CommitmentDetails.PeriodId
)
SELECT *
INTO #CommitmentPeriodCost
FROM [CommitmentPeriodCost]


SELECT

     ProjectId
    ,ProjectName
    ,'INV' as GroupRecordType
    ,'INV' as RecordType
    , PeriodId
    , Periods.Period
    , TotalThisInvoice AS TotalCost
    , (
            SELECT  SUM(TotalThisInvoice)
            FROM    #ProgressInvoicesPeriodCost AS RunningCost
            WHERE   ProjectId = ProgressInvoicesPeriodCost.ProjectId
                    AND PeriodId <= ProgressInvoicesPeriodCost.PeriodId
    ) AS RunningTotal

FROM
    #ProgressInvoicesPeriodCost ProgressInvoicesPeriodCost
LEFT OUTER JOIN Periods
            ON  ProgressInvoicesPeriodCost.PeriodId = Periods.Id
LEFT OUTER JOIN Projects
            ON ProgressInvoicesPeriodCost.ProjectId = Projects.Id

WHERE (Period <= @ToPeriod)
  AND ProgressInvoicesPeriodCost.ProjectId = @Projectlisting
  AND 'INV' = @RecordTypes

UNION ALL

SELECT
    ProjectId
    ,ProjectName
    ,'COM' as GroupRecordType
    ,'CO' as RecordType
    , PeriodId
    , Periods.Period
    , AmountApproved AS TotalCost
    , (
            SELECT  SUM(AmountApproved)
            FROM    #CommitmentCOPeriodCost AS RunningCost
            WHERE   ProjectId = CommitmentCOPeriodCost.ProjectId
                    AND PeriodId <= CommitmentCOPeriodCost.PeriodId
    ) AS RunningTotal

FROM
    #CommitmentCOPeriodCost CommitmentCOPeriodCost
LEFT OUTER JOIN Periods
            ON  CommitmentCOPeriodCost.PeriodId = Periods.Id
LEFT OUTER JOIN Projects
            ON CommitmentCOPeriodCost.ProjectId = Projects.Id

WHERE (Period <= @ToPeriod) and CommitmentCOPeriodCost.ProjectId = @Projectlisting
 AND 'CO' = @RecordTypes

UNION ALL

SELECT
    ProjectId
    ,ProjectName
    ,'COM' as GroupRecordType
    ,'COM' as RecordType
    , PeriodId
    , Periods.Period
    , TotalCost
    , (
            SELECT  SUM(TotalCost)
            FROM    #CommitmentPeriodCost AS RunningCost
            WHERE   ProjectId = CommitmentPeriodCost.ProjectId
                    AND PeriodId <= CommitmentPeriodCost.PeriodId
    ) AS RunningTotal

FROM
    #CommitmentPeriodCost CommitmentPeriodCost
LEFT OUTER JOIN Periods
            ON  CommitmentPeriodCost.PeriodId = Periods.Id
LEFT OUTER JOIN Projects
            ON CommitmentPeriodCost.ProjectId = Projects.Id

Where (Period <= @ToPeriod) and CommitmentPeriodCost.ProjectId = @Projectlisting
AND 'COM' = @RecordTypes

ORDER BY
    ProjectId
    , Period

结果：

尝试用您的数据重写，比较读取，如果您看到了差异，请告诉我们。

另一个关于“In”的词--它的表现不像你想的那样。因此，我在查询中将“引用”中的“全部”转换为“=”。如果这不适用于您，请为您的变量@Projectlisting、@ToPeriod和@RecordTypes张贴示例值。同时，您不妨回顾Erland Sommarskog关于这一主题的优秀文章。

http://www.sommarskog.se/arrays-in-sql.html

票数 5

页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://dba.stackexchange.com/questions/80963

复制

相似问题

问关于条款联盟的问题
EN

回答 2

Database Administration用户

Database Administration用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问关于条款联盟的问题EN

回答 2

Database Administration用户

Database Administration用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问关于条款联盟的问题
EN