You need to enable JavaScript to run this app. 如何编写SQL Server多对多桥接表的跨三表CRUD及检索查询
优惠活动
大模型
产品
解决方案
定价
更多

如何编写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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.07 15:45:11