創建具有多個連接的SQL視圖時出現問題-有解無憂

我正在嘗試創建一個視圖，它將作為 API 中 Excel 匯出的基礎。基本上，它包含的是有關特定專案的資訊。可以向所述專案添加計算（這一切都發生在前端的表單上）。這些計算稱為 EBIT、EBIT 和 OVI。用戶可以添加其中一個、兩個或全部，例如，將有只有 EBIT 的專案，也有只有 EBIT 的專案，也有只有 EBIT 和 OVI 的專案。View 需要在一行中回傳所有選擇的計算的專案資訊，因此由于用戶不會選擇某些型別的計算，因此還需要型別安全。

我的觀點代碼：

CREATE VIEW [Signoff].[ExecelReport_uvw]
    AS SELECT 
    project.ProjectName,
    project.CreatedOn,
    
    project.ProjectId,
    subCategory.SubCategoryName,

    projectStatus.StatusName,
    overallCategory.CategoryName,
    projectUserResponsible.UserName,
    valueImprovementType.ValueImprovementTypeName,
    OVI.OverallImprovementTypeName,
    project.NameOfSuplier,
    improvementCalculation.Baseline,
    improvementCalculation.ImpactValue,
    project.ContractStartDate,
    project.ContractEndDate,
    userBussinessController.UserName as BussinessControllerName,
    bussinessControllerStatus.ApprovalStatusName BussinessControllerStatus,
    userBussinesOwner.UserName as BussinessOwnerName,
    bussinesOwnerStatus.ApprovalStatusName as BussinessOwnerStatus,
    userBussinessCFO.UserName as BussinessCFOName,
    bussinessCFOStatus.ApprovalStatusName as BussinessCFOStatus,
    project.IsEbitda,
    improvementCalculation.EBITDA

    FROM [Signoff].[Project] as project
    LEFT JOIN [Signoff].[OverallImprovementType] as OVI on project.OverallImprovementTypeId = OVI.OverallImprovementTypeId
    LEFT JOIN [Signoff].[SubCategory] as subCategory on project.GPSubCategory = subCategory.SubCategoryId
    LEFT JOIN [Signoff].[Category] as overallCategory on  project.GPCategory = overallCategory.CategoryId
    LEFT JOIN [Signoff].[ValueImprovementType] as valueImprovementType on project.ValueImprovementTypeId = valueImprovementType.ValueImprovementTypeId
    
    LEFT JOIN [Signoff].[Status] as projectStatus on project.ProjectStatus = projectStatus.StatusId
    LEFT JOIN [Signoff].[User] as projectUserResponsible on project.ProjectResponsible = projectUserResponsible.UserId

    LEFT JOIN [Signoff].[ProjectUser] as projectUserBussinessControler on project.ProjectId = projectUserBussinessControler.ProjectId AND projectUserBussinessControler.ProjectRoleId = 'A36FC6CD-9ED7-4AA8-B1BE-355E48BDE25A'
    LEFT JOIN [Signoff].[User] as userBussinessController on projectUserBussinessControler.ApproverId = userBussinessController.UserId
    LEFT JOIN [Signoff].[ApprovalStatus] as bussinessControllerStatus on projectUserBussinessControler.ApprovalStatusId = bussinessControllerStatus.ApprovalStatusId 

    LEFT JOIN [Signoff].[ProjectUser] as projectUserBussinessOwner on project.ProjectId = projectUserBussinessOwner.ProjectId AND projectUserBussinessOwner.ProjectRoleId = 'E1E23E4F-1CA4-4869-9387-43CEDAEBBBB0'
    LEFT JOIN [Signoff].[User] as userBussinesOwner on projectUserBussinessOwner.ApproverId = userBussinesOwner.UserId
    LEFT JOIN [Signoff].[ApprovalStatus] as bussinesOwnerStatus on projectUserBussinessOwner.ApprovalStatusId = bussinesOwnerStatus.ApprovalStatusId 

    LEFT JOIN [Signoff].[ProjectUser] as projectUserBussinessCFO on project.ProjectId = projectUserBussinessCFO.ProjectId AND projectUserBussinessCFO.ProjectRoleId = 'DA17CF66-1D61-460E-BF87-5D86744DF22A'
    LEFT JOIN [Signoff].[User] as userBussinessCFO on projectUserBussinessCFO.ApproverId = userBussinessCFO.UserId
    LEFT JOIN [Signoff].[ApprovalStatus] as bussinessCFOStatus on projectUserBussinessCFO.ApprovalStatusId = bussinessCFOStatus.ApprovalStatusId 

    LEFT JOIN [Signoff].[ProjectImprovementCalculation] as projectImprovementCalculation on project.ProjectId = projectImprovementCalculation.ProjectId
    LEFT JOIN [Signoff].[ImprovementCalculation] as improvementCalculation on projectImprovementCalculation.ImprovementCalculationId = improvementCalculation.ImprovementCalculationId

改善計算表：

CREATE TABLE [Signoff].[ImprovementCalculation]
(
    [ImprovementCalculationId] INT NOT NULL IDENTITY,
    [Baseline] INT NOT NULL,
    [TotalSpend] INT NOT NULL,
    [ImpactValue] INT NOT NULL,
    [ImpactPercentage] INT NOT NULL,
    [EBITDA] INT NOT NULL,
    [CalculationType] VARCHAR (255) NOT NULL
)
GO

ALTER TABLE [Signoff].[ImprovementCalculation]
ADD CONSTRAINT [PK_ImprovemntCalculation] PRIMARY KEY([ImprovementCalculationId]);
GO

專案改進計算表：

CREATE TABLE [Signoff].[ProjectImprovementCalculation]
(
    [ProjectImprovementCalculationId] INT NOT NULL IDENTITY,
    [ProjectId] UNIQUEIDENTIFIER NOT NULL,
    [ImprovementCalculationId] INT NOT NULL,
)
GO

ALTER TABLE [Signoff].[ProjectImprovementCalculation]
ADD CONSTRAINT [PK_ProjectImprovementCalculation] PRIMARY KEY([ProjectImprovementCalculationId]);
GO

ALTER TABLE [Signoff].[ProjectImprovementCalculation]
ADD CONSTRAINT FK_ProjectProjectImprovementCalculation
FOREIGN KEY (ProjectId) REFERENCES [Signoff].[Project](ProjectId);
GO

ALTER TABLE [Signoff].[ProjectImprovementCalculation]
ADD CONSTRAINT FK_ImprovementCalculationProjectImprovementCalculation
FOREIGN KEY (ImprovementCalculationId) REFERENCES [Signoff].[ImprovementCalculation](ImprovementCalculationId);
GO

以防萬一，雖然我認為不需要，專案表：

CREATE TABLE [Signoff].[Project]
(
    [ProjectId] UNIQUEIDENTIFIER NOT NULL DEFAULT (NEWID()), 
    [ProjectName] NVARCHAR(50) NOT NULL, 
    [LegalEntity] UNIQUEIDENTIFIER NOT NULL,
    [ValueImprovementTypeId] INT NOT NULL, 
    [OverallImprovementTypeId] INT NOT NULL, 
    [NameOfSuplier] NVARCHAR(50) NOT NULL, 
    [ContractStartDate] DATE NOT NULL, 
    [ContractEndDate] DATE NOT NULL,
    [GPCategory] UNIQUEIDENTIFIER NOT NULL,
    [GPSubCategory] UNIQUEIDENTIFIER NOT NULL,
    [ProjectResponsible] UNIQUEIDENTIFIER NOT NULL,
    [ProjectNumber] INT,
    [FullProjectNumber] VARCHAR(55),
    [ProjectStatus] UNIQUEIDENTIFIER NOT NULL DEFAULT '05c2f392-8b69-4915-a166-c4418889f9e8', 
    [IsCanceled] BIT NULL DEFAULT 0,
    [IsEbitda] BIT NOT NULL DEFAULT 0, 
    [CreatedOn] DATETIME NOT NULL DEFAULT SYSDATETIME()
)
GO

ALTER TABLE [Signoff].[Project]
ADD CONSTRAINT [PK_Project] PRIMARY KEY([ProjectId]);
GO

ALTER TABLE [Signoff].[Project]
ADD CONSTRAINT [FK_ProjectStatus] FOREIGN KEY ([ProjectStatus]) REFERENCES [Signoff].[Status]([StatusId]);
GO

I have so came up with this solution, but it returns every single calculation in a different row in a table, and I want all calculations be in a single row with a project, so not what I am looking for:

LEFT JOIN [Signoff].[ProjectImprovementCalculation] as projectImprovementCalculation on project.ProjectId = projectImprovementCalculation.ProjectId
LEFT JOIN [Signoff].[ImprovementCalculation] as improvementCalculation on projectImprovementCalculation.ImprovementCalculationId = improvementCalculation.ImprovementCalculationId

創建具有多個連接的 SQL 視圖時出現問題

Does anyone knows how to do it? Or I am approaching a problem from completely wrong way? If the information I have written is a bit chaotic, something isn't understandable, I can rephrase it.

uj5u.com熱心網友回復：

我將假設可用的 CalculationType 值是固定的，每個專案的每種型別最多有一個改進計算，并且您希望為 BaseLine 和 ImpactValue 每個計算型別定義固定的專用列。

一種方法是使用嵌套連接，該連接將有效地左連接 ProjectImprovementCalculation 和 ImprovementCalculation 的 INNER JOINed 組合，用于每種計算型別。然后可以在最終選擇串列中單獨參考每個結果。

就像是：

SELECT ...
    IC_AAA.BaseLine, IC_AAA.ImpactValue,
    IC_BBB.BaseLine, IC_BBB.ImpactValue,
    ...
FROM ...
LEFT JOIN Signoff.ProjectImprovementCalculation as PIC_AAA
    JOIN Signoff.ImprovementCalculation as IC_AAA
        ON IC_AAA.ImprovementCalculationId = PIC_AAA.ImprovementCalculationId
        AND IC_AAA.CalculationType = 'AAA'
    ON PIC_AAA.ProjectId = project.ProjectId
LEFT JOIN Signoff.ProjectImprovementCalculation as PIC_BBB
    JOIN Signoff.ImprovementCalculation as IC_BBB
        ON IC_BBB.ImprovementCalculationId = PIC_BBB.ImprovementCalculationId
        AND IC_BBB.CalculationType = 'BBB'
    ON PIC_BBB.ProjectId = project.ProjectId
...

JOINs兩個后跟兩個ON子句的語法有點奇怪。如果允許括號會更清楚，但是（據我所知）這不是語法的一部分。

有幾種替代方案可以實作相同的效果。以下使用OUTER APPLY:

SELECT ...
    AAA.BaseLine, AAA.ImpactValue,
    BBB.BaseLine, BBB.ImpactValue,
    ...
FROM ...
OUTER APPLY (
    SELECT IC.*
    FROM Signoff.ProjectImprovementCalculation as PIC
    JOIN Signoff.ImprovementCalculation as IC
        ON IC.ImprovementCalculationId = PIC.ImprovementCalculationId
        AND IC.CalculationType = 'AAA'
    WHERE PIC.ProjectId = project.ProjectId
) AAA
OUTER APPLY (
    SELECT IC.*
    FROM Signoff.ProjectImprovementCalculation as PIC
    JOIN Signoff.ImprovementCalculation as IC
        ON IC.ImprovementCalculationId = PIC.ImprovementCalculationId
        AND IC.CalculationType = 'BBB'
    WHERE PIC.ProjectId = project.ProjectId
) BBB
...

使用公用表運算式 (CTE) 可以減少一些重復，并使查詢更具可讀性。

;WITH ImprovementCTE AS (
    SELECT PIC.ProjectId, IC.*
    FROM Signoff.ProjectImprovementCalculation as PIC
    JOIN Signoff.ImprovementCalculation as IC
        ON IC.ImprovementCalculationId = PIC.ImprovementCalculationId
)
SELECT ...
    AAA.BaseLine, AAA.ImpactValue,
    BBB.BaseLine, BBB.ImpactValue,
    ...
FROM ...
LEFT JOIN ImprovementCTE AAA
    ON AAA.ProjectId = project.ProjectId
    AND AAA.CalculationType = 'AAA'
LEFT JOIN ImprovementCTE BBB
    ON BBB.ProjectId = project.ProjectId
    AND BBB.CalculationType = 'BBB'
...

您也可以嘗試在單個中使用條件聚合CROSS APPLY：

SELECT ...
    IC.BaseLineAAA, IC.ImpactValueAAA,
    IC.BaseLineBBB, IC.ImpactValueBBB,
    ...
FROM ...
CROSS APPLY (
    SELECT
        BaseLineAAA = SUM(CASE WHEN IC.CalculationType = 'AAA' THEN IC.BaseLine),
        ImpactValueAAA = SUM(CASE WHEN IC.CalculationType = 'AAA' THEN IC.ImpactValue),
        BaseLineBBB = SUM(CASE WHEN IC.CalculationType = 'BBB' THEN IC.BaseLine),
        ImpactValueBBB = SUM(CASE WHEN IC.CalculationType = 'BBB' THEN IC.ImpactValue),
        ...
    FROM Signoff.ProjectImprovementCalculation as PIC
    JOIN Signoff.ImprovementCalculation as IC
        ON IC.ImprovementCalculationId = PIC.ImprovementCalculationId
    WHERE PIC.ProjectId = project.ProjectId
) IC

我希望還有其他方法，例如使用 PIVOT。

如果以上看起來滿足您的需求，您仍應運行測驗并檢查執行計劃以查看哪個執行計劃最好。即使選擇了專案子集，有些人可能傾向于檢索所有 ImprovementCalculation 行。

要處理缺失的計算型別，您可以使用該ISNULL()函式提供默認值。如果您需要在其他數字結果中強制使用空白值，您可能需要使用類似ISNULL(CONVERT(VARCHAR(50), result), '').

轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/452426.html

標籤：sql sql-server entity-framework tsql join

上一篇：CSV檔案修復了在插入SQLServer時不總是雙引號的欄位中的雙引號

下一篇：如何使用帶有INNERJOIN的UPDATE查詢來更新表中的欄位