使用链接表的SQL插入/删除行查询
我有4个sql表，它们基于一个共同的城市链接行。每个表行都有一个代码，这些代码将组合在一起，根据每个具有匹配城市的表行的代码值创建一个主代码。例如，如果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_4(部分)的table_1(标题)父级之间的任何位置插入或删除行。是否有人可以使用下面的表格来帮助我使用插入和删除查询来处理上面的这种情况。
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)回答 1
Stack Overflow用户
发布于 2019-04-15 12:49:52
我建议使用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也将更新。因此，在您的示例中，您需要将每个表的Code设置为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现在，让我们来尝试一下。
我们将在CityCode表中插入一个新城市。
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)进行了更新
我希望这能对你有所帮助
https://stackoverflow.com/questions/55681856
复制相似问题