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