如何编写SQL Server多对多桥接表的跨三表CRUD及检索查询
SQL Server 多对多关联结构CRUD实现方案
针对搭建的Case、Violence_type及桥接表Referral的三表多对多结构,以下是可直接复用的检索、跨表操作语句。
前置关联约定
- 主表1:
Case存储案件核心业务数据,表内主键为CaseID - 主表2:
Violence_type存储暴力类型字典数据,表内主键为ViolenceTypeID - 桥接表:
Referral作为两表的中间映射层,通过外键分别关联Case.CaseID与Violence_type.ViolenceTypeID,通常配置CaseID + ViolenceTypeID联合唯一约束,避免重复绑定无效数据。
通用数据检索
基础关联明细查询
获取所有案件对应的绑定暴力类型明细数据
SELECT c.CaseID, c.CaseTitle, c.ReportTime, vt.ViolenceTypeID, vt.TypeName FROM [Case] c INNER JOIN Referral r ON c.CaseID = r.CaseID INNER JOIN Violence_type vt ON r.ViolenceTypeID = vt.ViolenceTypeID;
单案件维度聚合查询
按案件维度展示所有绑定的暴力类型,避免同案件多行重复展示
SELECT c.CaseID, c.CaseTitle, c.ReportTime, STRING_AGG(vt.TypeName, '、') AS BoundViolenceTypes FROM [Case] c LEFT JOIN Referral r ON c.CaseID = r.CaseID LEFT JOIN Violence_type vt ON r.ViolenceTypeID = vt.ViolenceTypeID GROUP BY c.CaseID, c.CaseTitle, c.ReportTime;
字典维度反查统计
统计每个暴力类型下关联的案件数量
SELECT vt.ViolenceTypeID, vt.TypeName, COUNT(r.CaseID) AS RelatedCaseCount FROM Violence_type vt LEFT JOIN Referral r ON vt.ViolenceTypeID = r.ViolenceTypeID GROUP BY vt.ViolenceTypeID, vt.TypeName;
跨三表的完整CRUD操作
所有涉及多表联动的操作必须包裹显式事务,保证数据一致性,避免出现主表写入成功、关联表写入失败的孤数据问题。
新增操作
实现逻辑:先写入主表Case的案件基础数据,获取生成的自增案件ID后,再向桥接表写入该案件对应的暴力类型绑定关系。
BEGIN TRANSACTION -- 写入案件主数据 INSERT INTO [Case] (CaseTitle, CaseContent, ReportTime, ReporterName) VALUES ('某社区伤人事件', '事件具体调查详情', GETDATE(), '社区工作人员'); -- 获取刚生成的案件ID DECLARE @newCaseId INT = SCOPE_IDENTITY(); -- 写入桥接表绑定关系,示例中该案件涉及2种暴力类型(ID为1、3) INSERT INTO Referral (CaseID, ViolenceTypeID, LinkTime) VALUES (@newCaseId, 1, GETDATE()), (@newCaseId, 3, GETDATE()); COMMIT TRANSACTION
注意:如果绑定的暴力类型在
Violence_type表中不存在,需先在字典表新增对应类型,否则会触发外键约束报错。
更新操作
常见场景为同时更新案件基础信息、调整案件绑定的暴力类型集合,逻辑为先更新主表字段,再同步替换桥接表的关联关系。
BEGIN TRANSACTION -- 更新案件基础字段 UPDATE [Case] SET CaseTitle = '更新后的案件标题', CaseContent = '更新后的调查详情' WHERE CaseID = 12; -- 指定需更新的案件ID -- 清除该案件原有的暴力类型绑定 DELETE FROM Referral WHERE CaseID = 12; -- 重新写入调整后的暴力类型绑定 INSERT INTO Referral (CaseID, ViolenceTypeID, LinkTime) VALUES (12, 2, GETDATE()), (12, 4, GETDATE()), (12, 6, GETDATE()); COMMIT TRANSACTION
删除操作
因外键约束限制,需先删除桥接表的关联记录,再删除主表数据,否则会触发外键依赖报错。
BEGIN TRANSACTION -- 先清除对应案件的所有桥接关联 DELETE FROM Referral WHERE CaseID = 12; -- 再删除主表的案件记录 DELETE FROM [Case] WHERE CaseID = 12; COMMIT TRANSACTION
操作注意事项
- 桥接表建议添加
CaseID + ViolenceTypeID的联合唯一索引,从表结构层面避免同一案件重复绑定同一类型的无效数据 - 如果检索需要包含未绑定任何暴力类型的空关联案件,需使用
LEFT JOIN替代INNER JOIN,否则会过滤掉无关联的案件记录 - 若需要删除某类暴力类型,需遵循相同的删除顺序:先清除桥接表中关联该类型ID的所有记录,再删除字典表中的类型条目
内容的提问来源于stack exchange,提问作者S. Omer Sadaat
相关产品推荐
相关产品推荐

