sql-server - 在 Ancestor 表中插入数据并从 Ancestor 表中检索数据

Question

我正在尝试为先决条件单元创建一个祖先表。我不确定如何使用 SQL 脚本为 2 级、3 级等插入数据...

我有三个表:

1. Unit_Of_Competency(Unit_Of_Competency_ID:uniqueidentifier，UoC_Code:varchar(20)，UoC_Title:varchar(200)，描述符:varchar(500))
2. 先决条件(ID:uniqueidentifier，Unit_Of_Competency_ID:外键引用 Unit_Of_Competency_ID，Prerequisite_ID:外键引用 Unit_Of_Competency_ID)
3. Prerequisite_Ancestor(Prerequisite_Ancestor_ID、Unit_Of_Competency_ID:外键引用 Unit_Of_Competency_ID、Prerequisite_ID:外键引用 Unit_Of_Competency_ID)

能力表单元有以下数据:

先决条件表具有以下数据:

加入 Unit_Of_Competency 和先决条件后，我们得到以下输出:

我需要填充 Prerequisite_Ancestor 表；我只填充到 1 级

我需要填充级别 2、3、4 的祖先表例如，

1. 贝叶斯定理 ||事业部|| 2
2. 概率||数学 || 2
3. 贝叶斯定理 ||数学 || 3

我在 Prerequisite_Ancestor 表中输入 Ancestor_Level 1 的方式是使用以下脚本:

INSERT INTO Prerequisite_Ancestor (Prerequisite_Ancestor_ID, UoC_ID, Prerequisite_ID, Ancestor_Level)
select newID(), U.Unit_Of_Competency_ID as UoC, P.Prerequisite_ID as Prerequisite, 1 from Prerequisite P 
join Unit_Of_Competency U on P.Unit_Of_Competency_ID=U.Unit_Of_Competency_ID
join Unit_Of_Competency U2 on U2.Unit_Of_Competency_ID=P.Prerequisite_ID

如何使用脚本进入第 2、3 级......？

提前谢谢你:)

Answer 1

您需要创建另一个用于暂存目的的表格，该表格将从您设计的 Web 表单中收集数据。并且每次都截断以提交新的表单。登台表将包含您需要的所有列，以便填充所有三个表。查看您现有的列，暂存表看起来包含以下列:

CREATE TABLE LoadUnitsStg(
    StagingID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT NEWID(),
    UoC_Code VARCHAR(50),
    UoC_Title VARCHAR(200),
    UoC_Descriptor VARCHAR(500),
    Pre_UoC_Code VARCHAR(50),
    Pre_UoC_PreReq VARCHAR(50),
    Pre_Pathway VARCHAR(50),
    Anc_UoC_Code VARCHAR(50),
    Anc_UoC_PreReq VARCHAR(50),
    Ancestor_level INT
    )

现在将数据从 Web 表单加载到暂存表，并使用此查询将数据填充到所需的表中。

INSERT INTO Unit_Of_Competency ([UoC_Code],[UoC_Title],[Descriptor])
        SELECT DISTINCT(UoC_Code),UoC_Title,UoC_Descriptor FROM LoadUnitsStg stg
            WHERE UoC_Code IS NOT NULL AND NOT EXISTS (
                SELECT 1 FROM Unit_Of_Competency UoC
                    WHERE UoC.UoC_Code=stg.UoC_Code);

    INSERT INTO Prerequisite ([Pathway],[Unit_Of_Competency_ID],[Prerequisite_ID])
        SELECT DISTINCT(Pre_Pathway),(Select Unit_Of_Competency_ID from Unit_Of_Competency where UoC_Code like stg.Pre_UoC_Code),(Select Unit_Of_Competency_ID as Prerequisite_ID from Unit_Of_Competency where UoC_Code like stg.Pre_UoC_PreReq) FROM LoadUnitsStg stg
            WHERE Pre_Pathway IS NOT NULL AND NOT EXISTS (
                SELECT 1 FROM Prerequisite Pre
                    WHERE Pre.Pathway=stg.Pre_Pathway);

    INSERT INTO Prerequisite_Ancestor ([Ancestor_Level],[UoC_ID],[Prerequisite_ID])
        SELECT DISTINCT(Ancestor_level),(Select Unit_Of_Competency_ID as UoC_ID from Unit_Of_Competency where UoC_Code like stg.Anc_UoC_Code),(Select Unit_Of_Competency_ID as Prerequisite_ID from Unit_Of_Competency where UoC_Code like stg.Anc_UoC_PreReq) FROM LoadUnitsStg stg
            WHERE Ancestor_level IS NOT NULL AND NOT EXISTS (
                SELECT 1 FROM Prerequisite_Ancestor Anc
                    WHERE Anc.Ancestor_level=stg.Ancestor_level);

加载数据后，只需为下一组条目截断暂存表。

希望对你有帮助。