使用链接表的 SQL 插入/删除行查询

Question

我有 4 个 sql 表，它们基于一个公共城市链接了行。每个表行都有一个代码，该代码将组合在一起，根据每个表行的代码值创建 1 个主代码，并具有匹配的城市。例如，如果与休斯顿市匹配的 Table_1.code = 2、Table_2.code = 3c、Table_3.code = 1a 和 Table_4.code = 2(2)，则组合的最终结果代码值将为 2-3c-1a-2(2)。我有一个使用内部联接的查询，该查询可以很好地基于匹配的城市创建组合代码。我的问题是，有时我不会有城市的子表 2(章节)、3(部分)或 4(部分)代码行，因为它可能会稍后出现。如何进行插入的最佳方法，我可以在其中添加与父行的城市匹配的表 2(章节)、3(部分)或 4(部分)代码行table_1并将更新其他表上的所有 FK 引用，以便表行按城市链接在一起以进行另一个查询，以便稍后在需要时获取组合代码。我不是SQL编程专家，但想知道是否有人可以指导我如何做到这一点。希望我已经正确设置了表，以便每一行都具有对列表中与城市代码匹配的上一个/下一个表的 FK 引用，因此可以在特定城市的table_1(标题)父级到 table_4(部分) 之间的任何位置插入或删除行。有人可以使用下表帮助我插入和删除查询来处理上述情况。

Table1-Title (Parent table)
table1_ID_PK int,
code varchar(100),
city varchar(100)
Table2-Chapter (Child table)
table2_ID_PK int,
table1_ID_FK int,
table3_ID_FK int,
code varchar(100),
city varchar(100)
Table3-Part (Child table)
table3_ID_PK int,
table1_ID_FK int,
table2_ID_FK int,
table4_ID_FK int,
code varchar(100),
city varchar(100)
Table4-Section (Child table)
table4_ID_PK int,
table1_ID_FK int,
table2_ID_FK int,
table3_ID_FK int,
code varchar(100),
city varchar(100)

Answer 1

我能建议的是使用 VIEW，这样只要视图中所有部分都可用，您就可以将它们插入到您想要的任何位置。视图将始终自动更新。因此，您可以使用当前查询，并将其调整为在视图中使用它，然后查询该视图，如果它具有您需要的完整代码，则可以将该行插入到目标表中。

例：

CREATE VIEW CityMasterCode AS (
SELECT 
t1.City 
,   CASE WHEN t1.code IS NULL OR t1.code = '' THEN NULL ELSE t1.code +  '-' END 
+   CASE WHEN t2.code IS NULL OR t2.code = '' THEN NULL ELSE t2.code +  '-' END 
+   CASE WHEN t3.code IS NULL OR t3.code = '' THEN NULL ELSE t3.code +  '-' END 
+   CASE WHEN t4.code IS NULL OR t4.code = '' THEN NULL ELSE t4.code END AS MasterCode
FROM 
table1 t1
LEFT JOIN table2 t2 ON t2.table1_ID_FK = t1.table1_ID_PK
LEFT JOIN table3 t3 ON t3.table1_ID_FK = t1.table1_ID_PK
LEFT JOIN table4 t4 ON t4.table1_ID_FK = t1.table1_ID_PK
)

INSERT INTO TargetTable (city, code)
SELECT 
m.City 
,   m.MasterCode
FROM table1 t1 
JOIN CityMasterCode m ON m.City = t1.City AND t1.code = m.MasterCode

更新在评论中回答您的问题。如果要使用更新级联，则需要将 FK 操作设置为更新级联。这意味着，每当PK更新时，其所有FK也将更新。因此，在您的情况下，您需要将每个表的代码设为 PK，这也意味着您的代码列将始终是唯一的。对于你的方案，此方法不是一个好主意。

但是，既然我有你想做的想法(有点)，我建议改变你的思维方式。您可以只使用一个包含标题、章节、部件和章节代码的表，而不是走这条路。并根据需要更新它们。这对您来说会容易得多。

它会是这样的：

MasterCityCode (Child Table)
ID_PK int,
table1_ID_FK int,
city varchar(100), 
TitleCode varchar(100),
ChapterCode varchar(100),
PartCode varchar(100),
SectionCode varchar(100)

父表将保存城市和主代码，每当 MasterCityCode 表中的所有 4 个部分完成时，这些代码都会更新(触发器对于这部分很有用)。

快速示例：

首先创建所需的表：

CREATE TABLE CityCode (
ID INT IDENTITY(1,1) NOT NULL,
City VARCHAR(100) NOT NULL, 
Code VARCHAR(100) NULL,
PRIMARY KEY (ID, City)  
)
CREATE TABLE CityCode_Master (
ID INT IDENTITY(1,1) NOT NULL,
CityCode_ID_FK INT NOT NULL,
CityCode_City_FK  VARCHAR(100) NOT NULL, 
TitleCode VARCHAR(100) NULL,
ChapterCode VARCHAR(100) NULL,
PartCode VARCHAR(100) NULL,
SectionCode VARCHAR(100) NULL, 
PRIMARY KEY (ID),
FOREIGN KEY (CityCode_ID_FK, CityCode_City_FK) REFERENCES CityCode(ID, City)
ON UPDATE CASCADE 
ON DELETE CASCADE
)   

现在，我们将设置两个触发器，一个在CityCode上，它将CityCode_Master表中插入所有新城市。另一个将在CityCode_Master上，它将处理CityCode.code更新。因此，如果所有代码部分都不为空，那么它将使用新的主代码更新 CityCode.code。

CREATE TRIGGER InsertNewRowInCityCode_Master
ON  CityCode
AFTER INSERT
AS 
BEGIN
SET NOCOUNT ON;
INSERT INTO CityCode_Master (CityCode_ID_FK, CityCode_City_FK)  
SELECT ID, City
FROM 
inserted
END
GO
CREATE TRIGGER UpdateCityCode
ON  CityCode_Master
FOR UPDATE
AS 
BEGIN
SET NOCOUNT ON;
IF EXISTS (
SELECT * 
FROM inserted 
WHERE 
TitleCode IS NOT NULL 
AND ChapterCode IS NOT NULL 
AND PartCode IS NOT NULL 
AND SectionCode IS NOT NULL     
)
BEGIN 
UPDATE city
SET Code = TitleCode + '-' + ChapterCode + '-' + PartCode + '-' +  SectionCode
FROM CityCode city 
JOIN inserted ins ON ins.CityCode_ID_FK = city.ID AND ins.CityCode_City_FK = city.City 
END 
END

现在，让我们尝试一下。

我们将在城市代码表中插入一个新城市。

INSERT INTO CityCode (City)
VALUES ('Houston')

如果查询 CityCode 表，则会发现休斯顿的代码为 NULL，这是我们第一阶段所需的代码，如果查询CityCode_Master表，则会发现休斯顿的新行，所有其他代码列均为 NULL。

现在，让我们更新CityCode_Master代码。

对于单个代码：

-- Update a single column at time. 
UPDATE CityCode_Master
SET TitleCode = '2'
WHERE 
CityCode_ID_FK = 1 
AND CityCode_City_FK = 'Houston'

如果重新检查该表，您会发现只有列 TitleCode 已更新，其余代码仍为空，如果重新检查 CityCode 表，您仍会看到休斯顿的代码仍为空。

现在让我们更新其余部分以查看第二个触发器的影响：

UPDATE CityCode_Master
SET 
ChapterCode = '3c'
,   PartCode    = '1a'
,   SectionCode = '2(2)'
WHERE 
CityCode_ID_FK = 1 
AND CityCode_City_FK = 'Houston'

您将看到，CityCode_Master现在所有代码都不为空。如果返回到 CityCode 表并重新检查它，您会发现休斯顿的代码列现在已使用主代码2-3c-1a-2(2)

我希望这对你有帮助