如何实现当Action Taken为See Previous Actions时复制SQL行并填充数据？

要实现将Action Taken为"See Previous Actions"的行替换为同案例同参与者的历史操作记录的需求，我们可以通过拆分查询+UNION ALL合并的方式完成，具体逻辑如下：

核心思路 #

• 第一部分：保留所有ActionTaken不等于"See Previous Actions"的原始记录
• 第二部分：针对ActionTaken等于"See Previous Actions"的记录，关联同CaseNumber、同CaseParticipantID下的其他有效操作记录，保留原行的非操作字段，仅替换ActionTaken为历史操作值
• 最后用UNION ALL合并两部分结果，得到最终数据集

完整SQL代码 #

-- 第一部分：保留正常的非"See Previous Actions"记录
SELECT
     Cases.CaseNumber
    ,Cases.DateOpened
    ,Cases.DateClosed
    ,Participant.CaseParticipantId
    ,Participant.RoleInCase
    ,Participant.RelToOrganization
    ,Participant.Practice
    ,Participant.City
    ,Issues.Issue
    ,Issues.IssueSubCategory
    ,Issues.CaseIssueId
    ,Issues.Outcome
    ,ParticipantAlias.ParticipantName
    ,CaseAction.ActionTaken
FROM [Warehouse].[Table].[Case] as Cases
LEFT JOIN [Warehouse].[Table].[CaseParticipant] as Participant
    ON Cases.CaseNumber = Participant.CaseNumber
LEFT JOIN [Warehouse].[Table].[CaseIssue] as Issues
    ON Participant.CaseParticipantId = Issues.CaseParticipantId
LEFT JOIN [Warehouse].[Table].[CaseAction] as CaseAction
    ON Issues.CaseIssueId = CaseAction.CaseIssueId
LEFT JOIN [Warehouse].[Table].[ParticipantAlias] as ParticipantAlias
    ON Participant.ParticipantAliasId = ParticipantAlias.ParticipantAliasId
WHERE Cases.CaseNumber = '123'
  AND CaseAction.ActionTaken != 'See Previous Actions'

UNION ALL

-- 第二部分：处理"See Previous Actions"的替换逻辑
SELECT
     Cases.CaseNumber
    ,Cases.DateOpened
    ,Cases.DateClosed
    ,Participant.CaseParticipantId
    ,Participant.RoleInCase
    ,Participant.RelToOrganization
    ,Participant.Practice
    ,Participant.City
    -- 保留原违规类别对应的Issue字段
    ,OriginalIssues.Issue
    ,OriginalIssues.IssueSubCategory
    ,OriginalIssues.CaseIssueId
    ,OriginalIssues.Outcome
    ,ParticipantAlias.ParticipantName
    -- 替换为同参与者同案例下的历史操作记录
    ,HistoryActions.ActionTaken
FROM [Warehouse].[Table].[Case] as Cases
LEFT JOIN [Warehouse].[Table].[CaseParticipant] as Participant
    ON Cases.CaseNumber = Participant.CaseNumber
-- 定位需要替换的原始行
LEFT JOIN [Warehouse].[Table].[CaseIssue] as OriginalIssues
    ON Participant.CaseParticipantId = OriginalIssues.CaseParticipantId
LEFT JOIN [Warehouse].[Table].[CaseAction] as OriginalActions
    ON OriginalIssues.CaseIssueId = OriginalActions.CaseIssueId
    AND OriginalActions.ActionTaken = 'See Previous Actions'
-- 关联同参与者同案例下的其他有效操作记录
LEFT JOIN [Warehouse].[Table].[CaseIssue] as HistoryIssues
    ON Participant.CaseParticipantId = HistoryIssues.CaseParticipantId
    AND HistoryIssues.CaseIssueId != OriginalIssues.CaseIssueId
LEFT JOIN [Warehouse].[Table].[CaseAction] as HistoryActions
    ON HistoryIssues.CaseIssueId = HistoryActions.CaseIssueId
    AND HistoryActions.ActionTaken != 'See Previous Actions'
LEFT JOIN [Warehouse].[Table].[ParticipantAlias] as ParticipantAlias
    ON Participant.ParticipantAliasId = ParticipantAlias.ParticipantAliasId
WHERE Cases.CaseNumber = '123'
  AND OriginalActions.ActionTaken = 'See Previous Actions'

代码说明 #

1. 第一部分查询：直接过滤掉标记为"See Previous Actions"的记录，保留所有正常操作行。
2. 第二部分查询：
   • 先定位所有需要替换的原始行，通过OriginalActions的条件筛选
   • 关联同一个参与者、同一个案例下的其他操作记录，排除当前需要替换的Issue本身，避免循环引用
   • 保留原行的违规类别等核心字段，仅将ActionTaken替换为历史操作的值
3. UNION ALL：合并两部分结果，不做去重处理，确保所有符合要求的记录都被保留，对应示例中最终得到7条数据的结果。

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