CTE应用实践(一)

1、在比较复杂的系统中，如果不方便更改现有的数据库。可以采用CTE来代替无法创建新视图的问题，例如如下代码，

下述代码中建立了多个CTE，作用类似于建立了多个视图，这种方法执行效率要远高于select子查询的效率。而且这种代码的编写方法结构更清晰，如果有逻辑功能的改变更容易修改和维护，可读性更高。

WITH ProTask
 AS (
    SELECT
        dbo.OuMap.Describle,
        project.OUID,
        project.PROJECTID,
        project.PROJECTNAME,
        project.PROJECTNUMBER,
        dbo.TASK.TASKID,
        task.TASKNAME,
        dbo.TASK.TASKNUMBER
    FROM dbo.TASK
    inner JOIN dbo.PROJECT
    ON task.PROJECTID=dbo.PROJECT.PROJECTID
    LEFT JOIN oumap
    ON task.CARRYINGOUTORGANIZATIONID = dbo.OuMap.OracleCode
)
,Billing_sumAR_Type AS
(
    SELECT dbo.Billing_SUMAR.*,dbo.Billing_Code.BillType FROM dbo.Billing_SUMAR LEFT OUTER JOIN dbo.Billing_Code
    ON dbo.Billing_SUMAR.BillCodeID = dbo.Billing_Code.BillCodeID
    WHERE dbo.Billing_Code.BillTypeID='AR'
    AND dbo.Billing_sumAR.Currency=@currency
)
,Billing_sumAP_Type AS
(
    SELECT dbo.Billing_SUMAP.*,billing_code.BillType
    FROM dbo.Billing_SUMAP LEFT OUTER JOIN dbo.Billing_Code
    ON dbo.Billing_SUMAP.BillCodeID = dbo.Billing_Code.BillCodeID
    WHERE dbo.Billing_Code.BillTypeID='AP'
    AND dbo.Billing_sumAP.Currency=@currency
)
,sumAR
 AS
(
    SELECT
        Billing_SUMAR_Type.OU,
        Billing_SUMAR_Type.ProjectID ,
        Billing_SUMAR_Type.TaskID,
        SUM(CASE Billing_sumAR_Type.BillType WHEN 'BGF' THEN ListPrice*quantity*Rate END ) AS '关务费',
        SUM(CASE Billing_sumAR_Type.BillType WHEN 'BXF' THEN ListPrice*quantity*Rate END ) AS '保险',
        SUM(CASE Billing_sumAR_Type.BillType WHEN 'CGF' THEN ListPrice*quantity*Rate END ) AS '仓库管理',
        SUM(CASE Billing_sumAR_Type.BillType WHEN 'CWF' THEN ListPrice*quantity*Rate END ) AS '财务费用',
        SUM(CASE Billing_sumAR_Type.BillType WHEN 'CZF' THEN ListPrice*quantity*Rate END ) AS '仓租费',
        SUM(CASE Billing_sumAR_Type.BillType WHEN 'DLF' THEN ListPrice*quantity*Rate END ) AS '代理费',
        SUM(CASE Billing_sumAR_Type.BillType WHEN 'GQF' THEN ListPrice*quantity*Rate END ) AS '港区费用',
        SUM(CASE Billing_sumAR_Type.BillType WHEN 'HCF' THEN ListPrice*quantity*Rate END ) AS '流耗材费',
        SUM(CASE Billing_sumAR_Type.BillType WHEN 'JBF' THEN ListPrice*quantity*Rate END ) AS '加班',
        SUM(CASE Billing_sumAR_Type.BillType WHEN 'YCF' THEN ListPrice*quantity*Rate END ) AS '异常费用',
        SUM(CASE Billing_sumAR_Type.BillType WHEN 'YSF' THEN ListPrice*quantity*Rate END ) AS '运输服务',
        SUM(CASE Billing_sumAR_Type.BillType WHEN 'ZZF' THEN ListPrice*quantity*Rate END ) AS '仓库增值服务',
        SUM(CASE WHEN Billing_sumAR_Type.BillType!='BGF'
            AND Billing_sumAR_Type.BillType!='BXF'
            AND Billing_sumAR_Type.BillType!='CGF'
            AND Billing_sumAR_Type.BillType!='CWF'
            AND Billing_sumAR_Type.BillType!='CZF'
            AND Billing_sumAR_Type.BillType!='DLF'
            AND Billing_sumAR_Type.BillType!='GQF'
            AND Billing_sumAR_Type.BillType!='HCF'
            AND Billing_sumAR_Type.BillType!='JBF'
            AND Billing_sumAR_Type.BillType!='YCF'
            AND Billing_sumAR_Type.BillType!='YSF'
            AND Billing_sumAR_Type.BillType!='ZZF'
        THEN ListPrice*quantity*Rate END ) AS '其他'

    FROM Billing_SUMAR_Type
    WHERE TaskID IS NOT NULL
        AND CreateTime>=@startdate AND CreateTime<=@enddate
        AND Status=70
    GROUP BY OU,ProjectID,TaskID
)
,sumAP
 AS
(
    SELECT
        Billing_SUMAP_Type.OU,
        Billing_SUMAP_Type.ProjectID,
        Billing_SUMAP_Type.TaskID,
        SUM(CASE Billing_sumAP_Type.BillType WHEN 'BGF' THEN ListPrice*quantity*Rate END ) AS '关务费',
        SUM(CASE Billing_sumAP_Type.BillType WHEN 'BXF' THEN ListPrice*quantity*Rate END ) AS '保险',
        SUM(CASE Billing_sumAP_Type.BillType WHEN 'CGF' THEN ListPrice*quantity*Rate END ) AS '仓库管理',
        SUM(CASE Billing_sumAP_Type.BillType WHEN 'CWF' THEN ListPrice*quantity*Rate END ) AS '财务费用',
        SUM(CASE Billing_sumAP_Type.BillType WHEN 'CZF' THEN ListPrice*quantity*Rate END ) AS '仓租费',
        SUM(CASE Billing_sumAP_Type.BillType WHEN 'DLF' THEN ListPrice*quantity*Rate END ) AS '代理费',
        SUM(CASE Billing_sumAP_Type.BillType WHEN 'GQF' THEN ListPrice*quantity*Rate END ) AS '港区费用',
        SUM(CASE Billing_sumAP_Type.BillType WHEN 'HCF' THEN ListPrice*quantity*Rate END ) AS '流耗材费',
        SUM(CASE Billing_sumAP_Type.BillType WHEN 'JBF' THEN ListPrice*quantity*Rate END ) AS '加班',
        SUM(CASE Billing_sumAP_Type.BillType WHEN 'YCF' THEN ListPrice*quantity*Rate END ) AS '异常费用',
        SUM(CASE Billing_sumAP_Type.BillType WHEN 'YSF' THEN ListPrice*quantity*Rate END ) AS '运输服务',
        SUM(CASE Billing_sumAP_Type.BillType WHEN 'ZZF' THEN ListPrice*quantity*Rate END ) AS '仓库增值服务',
        SUM(CASE WHEN Billing_sumAP_Type.BillType!='BGF'
            AND Billing_sumAP_Type.BillType!='BXF'
            AND Billing_sumAP_Type.BillType!='CGF'
            AND Billing_sumAP_Type.BillType!='CWF'
            AND Billing_sumAP_Type.BillType!='CZF'
            AND Billing_sumAP_Type.BillType!='DLF'
            AND Billing_sumAP_Type.BillType!='GQF'
            AND Billing_sumAP_Type.BillType!='HCF'
            AND Billing_sumAP_Type.BillType!='JBF'
            AND Billing_sumAP_Type.BillType!='YCF'
            AND Billing_sumAP_Type.BillType!='YSF'
            AND Billing_sumAP_Type.BillType!='ZZF'
        THEN ListPrice*quantity*Rate END ) AS '其他'
    FROM Billing_SUMAP_Type
    WHERE TaskID IS NOT NULL
        AND CreateTime>=@startdate AND CreateTime<=@enddate
        AND Status=70
    GROUP BY OU,ProjectID,TaskID
)
SELECT
    ProTask.OUID,
    ProTask.Describle,
    ProTask.PROJECTID,
    ProTask.PROJECTNUMBER,
    ProTask.TASKID,
    ProTask.TASKNUMBER,
    sumAR.关务费,
    sumAR.保险,
    sumAR.仓库管理,
    sumar.财务费用,
    sumAR.仓租费,
    sumAR.代理费 ,
    sumAR.港区费用 ,
    sumAR.流耗材费 ,
    sumAR.加班,
    sumAR.异常费用 ,
    sumAR.运输服务,
    sumAR.仓库增值服务,
    sumAR.其他,
    --成本
    sumAP.关务费,
    sumAP.保险,
    sumAP.仓库管理,
    sumAP.财务费用,
    sumAP.仓租费,
    sumAP.代理费 ,
    sumAP.港区费用 ,
    sumAP.流耗材费 ,
    sumap.加班,
    sumAP.异常费用 ,
    sumAP.运输服务,
    sumAP.仓库增值服务,
    sumAP.其他,
    '70' AS 'Status'
 FROM ProTask
LEFT OUTER JOIN sumAR
    ON ProTask.OUID=sumAR.OU AND ProTask.PROJECTID = sumAR.PROJECTID AND ProTask.TASKID = sumAR.TASKID
LEFT OUTER JOIN sumAP
    ON ProTask.OUID=sumAP.OU AND ProTask.PROJECTID=sumap.ProjectID AND ProTask.TASKID=sumap.TaskID

END
如果各位有什么比较复杂的SQL查询可以尝试用这种方法。欢迎大家讨论！