如何在SQL中将医生与患者的一对多关系迁移为多对多关系

你现在的一对多设计没法满足双向查询需求，核心问题是没用到多对多关系的标准设计——中间关联表。直接在单表加外键只能记录单向的一对多，要支持双向的多对多，必须拆成三张表：

1. 表结构设计 #

医生表（Doctors） #

存储医生基础信息，主键用DoctorId：

CREATE TABLE Doctors (
    DoctorId INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100) NOT NULL,
    -- 可添加其他字段：职称、科室、联系方式等
);

患者表（Clients） #

存储患者基础信息，主键用ClientId，移除原有的DoctorId字段（该字段会限制患者关联多名医生）：

CREATE TABLE Clients (
    ClientId INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100) NOT NULL,
    -- 可添加其他字段：年龄、病历号、联系方式等
);

关联表（DoctorClientRelationships） #

专门存储医生与患者的绑定关系，核心是通过两个外键分别关联医生表和患者表：

-- 方案1：用复合主键（天然避免重复绑定）
CREATE TABLE DoctorClientRelationships (
    DoctorId INT NOT NULL FOREIGN KEY REFERENCES Doctors(DoctorId),
    ClientId INT NOT NULL FOREIGN KEY REFERENCES Clients(ClientId),
    PRIMARY KEY (DoctorId, ClientId),
    -- 可选：添加关联属性，比如绑定时间、关联类型（主诊/会诊）
    BindTime DATETIME DEFAULT GETDATE()
);

-- 方案2：用自增主键（适合后续需要给关联关系加更多扩展属性的场景）
-- CREATE TABLE DoctorClientRelationships (
--     RelationshipId INT PRIMARY KEY IDENTITY(1,1),
--     DoctorId INT NOT NULL FOREIGN KEY REFERENCES Doctors(DoctorId),
--     ClientId INT NOT NULL FOREIGN KEY REFERENCES Clients(ClientId),
--     UNIQUE(DoctorId, ClientId), -- 加唯一约束避免重复绑定
--     BindTime DATETIME DEFAULT GETDATE()
-- );

2. 常用查询示例 #

查询某医生的所有患者 #

SELECT c.*
FROM Doctors d
JOIN DoctorClientRelationships dcr ON d.DoctorId = dcr.DoctorId
JOIN Clients c ON dcr.ClientId = c.ClientId
WHERE d.DoctorId = @DoctorId;

用Dapper执行时，直接映射Clients对象集合即可，和你之前的一对多查询逻辑兼容。

查询某患者的所有医生 #

SELECT d.*
FROM Clients c
JOIN DoctorClientRelationships dcr ON c.ClientId = dcr.ClientId
JOIN Doctors d ON dcr.DoctorId = d.DoctorId
WHERE c.ClientId = @ClientId;

Dapper这边直接映射Doctors对象集合，就能实现你需要的“患者查询所属全部医生”的需求。

3. 关键注意事项 #

• 必须给关联表加唯一性约束（复合主键或单独的UNIQUE约束），防止同一组医生-患者被重复绑定。
• 保留外键约束，避免出现不存在的医生/患者ID，保证数据完整性。
• 如果后续需要记录关联的额外信息（比如绑定人、绑定原因），直接在关联表中添加字段即可，扩展性极强。

内容的提问来源于stack exchange，提问作者CodeMan03