SQL Server多表关联查询：获取指定邮件的所有回复数据

需求梳理 #

• 涉及三张表：
  • tblEmail：存储应用发送的主邮件和内部回复
  • tblReply：存储外部系统的邮件回复
  • tblOutgoing：关联应用内的回复与对应的外部回复
• 核心要求：排除原始邮件（示例中EmailID=1），仅返回指定邮件链的所有回复数据，支持传入原始邮件ID或任意回复ID作为查询参数

假设表结构（可根据实际调整） #

为了方便编写查询，先明确关键字段（如果你的表字段不同，替换对应名称即可）：

• tblEmail：EmailID(主键)、ParentEmailID(父邮件ID，原始邮件为NULL/0)、Content(邮件内容)
• tblReply：ReplyID(主键)、ReplyContent(外部回复内容)
• tblOutgoing：EmailID(关联tblEmail的应用回复ID)、ReplyID(关联tblReply的外部回复ID)

解决方案查询语句 #

DECLARE @TargetID INT = 2; -- 替换为你的目标ID（原始邮件ID/任意回复ID）

-- 递归获取整个邮件链的所有邮件ID，标记原始邮件
WITH EmailChain AS (
    -- 锚点：从目标ID开始
    SELECT 
        EmailID, 
        ParentEmailID,
        CASE WHEN ParentEmailID IS NULL THEN 1 ELSE 0 END AS IsOriginal
    FROM tblEmail
    WHERE EmailID = @TargetID

    UNION ALL

    -- 递归遍历：向上追溯到原始邮件
    SELECT 
        e.EmailID, 
        e.ParentEmailID,
        CASE WHEN e.ParentEmailID IS NULL THEN 1 ELSE 0 END AS IsOriginal
    FROM tblEmail e
    INNER JOIN EmailChain ec ON ec.EmailID = e.ParentEmailID

    UNION ALL

    -- 递归遍历：向下获取所有子回复
    SELECT 
        e.EmailID, 
        e.ParentEmailID,
        CASE WHEN e.ParentEmailID IS NULL THEN 1 ELSE 0 END AS IsOriginal
    FROM tblEmail e
    INNER JOIN EmailChain ec ON ec.ParentEmailID = e.EmailID
)
-- 合并内部回复和外部回复数据，排除原始邮件
SELECT
    'Internal' AS ReplyType,
    e.EmailID AS ReplyID,
    e.Content AS ReplyContent,
    NULL AS AssociatedExternalReplyID,
    NULL AS ExternalReplyContent
FROM tblEmail e
INNER JOIN EmailChain ec ON e.EmailID = ec.EmailID
WHERE ec.IsOriginal = 0

UNION ALL

SELECT
    'External' AS ReplyType,
    o.EmailID AS AssociatedInternalReplyID,
    NULL AS InternalReplyContent,
    r.ReplyID AS ExternalReplyID,
    r.ReplyContent AS ExternalReplyContent
FROM tblOutgoing o
INNER JOIN tblReply r ON o.ReplyID = r.ReplyID
INNER JOIN EmailChain ec ON o.EmailID = ec.EmailID
WHERE ec.IsOriginal = 0
ORDER BY ReplyType;

逻辑说明 #

1. 递归CTE EmailChain：不管传入的是原始邮件ID还是某个回复ID，都会遍历整个邮件线程，获取所有关联的邮件ID，并标记出原始邮件。
2. 内部回复查询：从tblEmail中筛选出邮件链中除原始邮件外的所有内部回复。
3. 外部回复查询：通过tblOutgoing关联邮件链中的应用回复，获取对应的外部回复数据。
4. 合并结果：用UNION ALL合并两类回复，统一返回格式，方便后续处理。

扩展提示 #

• 如果需要封装成可复用的逻辑，可以把@TargetID改成存储过程的输入参数。
• 可以根据业务需求添加额外字段，比如邮件发送时间、发送人等，只需要在SELECT语句中补充对应字段即可。

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