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查询可以尝试用这种方法。欢迎大家讨论！