返回不正确的结果

Question

对于没有DDL / DML语句来创建表的初始post表示歉意--现在修复了

我正在建立一个数据库，基本上记录当地设施的视察结果。“检查”有时可能导致“案例”的提出，如果它确定了缺陷。也可以在不进行检查的情况下提出案件，例如，如果公众提出申诉。一次检查可能导致多起案件-例如，如果该设施是一个当地公园，可能是因为(a)割草不当和(b)垃圾箱没有被倒空而引起的。

一个案例不能导致检查，但它可以导致进一步的情况，因此在我的case表中，我有DerivedFromInspectionID和DerivedFromCaseID列。

有两个表格:检查和GMCase (用于“地面维护情况”记录)

表的突出部分可按以下方式创建：

CREATE TABLE [Inspection](
    [InspectionID] [int] NOT NULL PRIMARY KEY,
    [InspectionDate] [datetime] NULL,
    [InspectionType] [varchar](10) NULL
    )

CREATE TABLE [GMCase](
    [CaseID] [int] NOT NULL PRIMARY KEY,
    [CaseDate] [datetime] NULL,
    [CaseType] [varchar](20) NULL,
    [DerivedFromCaseID] [int] NULL CONSTRAINT fk_Case_Case FOREIGN KEY REFERENCES GMCase (CaseID),
    [DerivedFromInspectionID] [int] NULL CONSTRAINT fk_Case_Inspection FOREIGN KEY REFERENCES Inspection(InspectionID)
    )


INSERT INTO Inspection(InspectionID, InspectionDate,InspectionType)
VALUES 
(2,GETDATE(),'Scheduled'),
(3,GETDATE(),'Scheduled'),
(5,GETDATE(),'Ad-hoc')


INSERT INTO GMCase (CaseID,CaseDate,CaseType,DerivedFromCaseID,DerivedFromInspectionID)
VALUES
(1 , GETDATE(),'Compliment',NULL,NULL),
(2, GETDATE(),'Complaint',NULL,2),
(3,GETDATE(),'Instruction',2,NULL),
(5,GETDATE(),'Rectification',3,NULL),
(14,GETDATE(),'Complaint',NULL,2)               

我想要创建一个视图，它将显示

[Inspection1] (Optionally)
    >>> Case1 
        >>> Case2 
            >>> Case3. 

当查看案例或检查细节时，这将在数据库应用程序中使用。

在应用程序中，我需要能够查看链中的任何一种情况，看看它如何适合该链。所以，如果我看Case2，它应该显示上面的所有项目。

为了获取源数据，我最初创建了一个包含UNION查询的视图，将检查表和GMCase表中的等效列组合在一起。由于我需要能够识别每一种情况/检查，所以我引入了CaseID和InspectionID --最初分为两个单独的列，但我认为有一个ID列是有用的。我想确保这个ID中没有明显的重复(包含2用于检查2，但也用于Case2)，所以我突然想到，如果ID是InspectionID (这可能是错误的？)

我添加了一个level列，以帮助识别一个案例是在根级别，还是从检查中派生出来的，并帮助作为CTE中级别计算的起点。

我还添加了一个名为RootCase的专栏，对于检查，允许我查看检查链接到的GMCase记录(如果有的话)。这是为了让我看一看案例3，例如，我可以看到它最终是从检验2派生出来的，因为它的根将与检验2的RootCase相同。我现在还在想，这是否是一个错误.

视图的SQL如下：

CREATE VIEW vwCaseHierarchySource 
AS
    SELECT CaseID, 
    CaseDate, 
    CaseType,
    ISNULL(-g.DerivedFromInspectionID,g.DerivedFromCaseID) AS ParentCaseID,
    CASE WHEN g.DerivedFromCaseID IS NULL AND g.DerivedFromInspectionID IS NULL THEN 0 ELSE 1 END AS [Level],
    CaseID AS RootCase
    FROM GMCase g
    UNION
    SELECT -i.InspectionID
          ,i.InspectionDate
          ,CONVERT(VARCHAR(20),i.InspectionType + ' inspection') AS CaseType
          ,NULL AS ParentCaseID
          ,CASE WHEN g.CaseID IS NULL THEN 0 ELSE 1 END AS [Level]
          ,g.CaseID AS RootCase
    FROM Inspection i
    LEFT JOIN GMCase g
    ON i.InspectionID = g.DerivedFromInspectionID

然后，我尝试了许多创建CTE的方法来显示这个层次结构。CTE不是我的强项，我不记得我尝试过的所有事情，但下面是我目前的努力。

我怀疑，我遇到的问题是，检查可能导致不止一个情况(在我的样本数据中确实导致)，因为每当我使用CTE时，我都会得到正确的信息，但也会得到不正确的信息。如果我试图得到案例2的结果，我得到了案例2的层次结构，但也得到了案例14的行。如果我尝试案例14，我得到了这个结果，但也得到了案例2的所有行。

这是CTE：

CREATE VIEW [dbo].[vwCaseInspectionHierarchy] 
AS

WITH CaseList AS 
(
SELECT
    CaseID,
    CaseDate,
    CaseType,
    ParentCaseID,
    RootCase,
    RootCase.[Level]
FROM vwCaseHierarchySource RootCase
WHERE RootCase.ParentCaseID IS NULL

UNION ALL

SELECT
    ChildCase.CaseID,
    ChildCase.CaseDate,
    ChildCase.CaseType,
    ChildCase.ParentCaseID,
    CaseList.RootCase,
    CaseList.[Level]+1
FROM vwCaseHierarchySource ChildCase
INNER JOIN CaseList ON
    ChildCase.ParentCaseID=CaseList.CaseID
)

SELECT * 
      ,REPLICATE(' ',[Level]*5) +   CaseType AS IndentedCaseType 
FROM CaseList

我的测试代码是：

SELECT *
FROM [vwCaseInspectionHierarchy] 
WHERE RootCase=2

有谁可以帮我？为一个长时间的帖子道歉，但希望我已经把所有相关的.

安德鲁

Answer 1

我不认为你真的需要那样的视角。

只要对表本身进行递归CTE，就可以做到这一点。

并且可以使用两个表中的类型字符串为递归注入种子。

示例片段：

declare @Inspection table (InspectionID int primary key, InspectionDate datetime, InspectionType varchar(30));
declare @GMCase table (CaseID int primary key, CaseDate date, CaseType varchar(30), DerivedFromCaseID int, DerivedFromInspectionID int);

insert into @Inspection (InspectionID, InspectionDate, InspectionType) values
(102, '2019-01-22 00:00:00.000', 'Scheduled'),
(103, '2019-02-06 14:25:55.133', 'Scheduled'),
(105, '2019-02-06 16:59:04.820', 'Ad-hoc');

insert into @GMCase (CaseID, CaseDate, CaseType, DerivedFromCaseID, DerivedFromInspectionID) values
(1,  '2019-01-23', 'Compliment', NULL, NULL),
(2,  '2018-12-04', 'Complaint', NULL, 102),
(3,  '2018-12-04', 'Instruction', 2, NULL),
(5,  '2018-12-21', 'Rectification', 3, NULL),
(14, '2019-02-06', 'Complaint', NULL, 2),
(15, '2019-02-07', 'Compliment', NULL, NULL),
(16, '2019-02-08', 'Complaint', 15, NULL);

 with RCTE as
(
   -- Seeding the recursive CTE
   select i.InspectionID, n as Lvl, t.CaseID as RootCaseID, case when n = 1 then t.CaseID end as CaseID, t.DerivedFromCaseID, iif(n=0, i.InspectionType, t.CaseType) as [Type]
   from @GMCase t
   left join @Inspection i ON i.InspectionID = t.DerivedFromInspectionID
   cross join (values (0),(1)) nums(n)
   where t.DerivedFromCaseID is null

   union all

   select r.InspectionID, r.Lvl + 1, r.RootCaseID, t.CaseID, t.DerivedFromCaseID, t.CaseType 
   from RCTE r
   join @GMCase t on t.DerivedFromCaseID = r.CaseID
)
select c.RootCaseID, c.CaseID, c.InspectionID, t.CaseDate,
concat(replicate(' ',c.Lvl*5),COALESCE([Type],'Inspection')) AS IndentedType 
from RCTE c
left join @GMCase t on t.CaseID = c.CaseID
order by c.RootCaseID, c.Lvl;

结果：

RootCaseID  CaseID  InspectionID    CaseDate    IndentedType
1           NULL        NULL        NULL        Inspection
1           1           NULL        2019-01-23       Compliment
2           NULL        102         NULL        Scheduled
2           2           102         2018-12-04       Complaint
2           3           102         2018-12-04            Instruction
2           5           102         2018-12-21                 Rectification
14          NULL        NULL        NULL        Inspection
14          14          NULL        2019-02-06       Complaint
15          NULL        NULL        NULL        Inspection
15          15          NULL        2019-02-07       Compliment
15          16          NULL        2019-02-08            Complaint