如何修改ProfileText表的PreviousText，使时间线仅包含已发布条目？

我来帮你搞定这个需求！你的ProfileText表现在是把所有状态的记录都串成时间线，现在要改成只链已发布（released）的条目，对吧？下面分几个部分来说明怎么做：

原来的逻辑是不管之前记录的状态，直接把上一条设为PreviousText，现在得调整成专门找最近的State = 'released'的记录作为新条目的PreviousText。

举个SQL示例（以MySQL为例），插入新条目时可以这么写：

INSERT INTO ProfileText (Text, State, PreviousText)
VALUES (
    '新的文本内容',
    'released', -- 哪怕是草稿状态，也可以用这个逻辑找最近的已发布记录
    (SELECT Id FROM ProfileText WHERE State = 'released' ORDER BY Id DESC LIMIT 1)
);

如果是第一次创建已发布记录，PreviousText会是NULL，这很合理——作为时间线的起点就行。要是用应用层代码（比如Java/C#）实现，逻辑就是先查询数据库里最新的已发布记录ID，再插入新条目时把这个ID赋值给PreviousText。

如果之前已经有不少旧数据，它们的PreviousText可能指向了草稿记录，这时候可以用脚本或SQL批量修正：
比如用递归CTE（PostgreSQL/MySQL 8+支持）来修正所有已发布记录的PreviousText，让它们只链已发布的前驱：

WITH RECURSIVE ReleasedCTE AS (
    -- 找到最早的已发布记录作为时间线起点
    SELECT Id, Text, State, PreviousText, 0 AS level
    FROM ProfileText
    WHERE State = 'released' AND PreviousText IS NULL
    UNION ALL
    -- 依次更新后续已发布记录的PreviousText为上一条已发布记录的ID
    SELECT pt.Id, pt.Text, pt.State, rcte.Id AS NewPreviousText, rcte.level + 1
    FROM ProfileText pt
    JOIN ReleasedCTE rcte ON pt.PreviousText = rcte.Id
    WHERE pt.State = 'released'
)
UPDATE ProfileText pt
JOIN ReleasedCTE rcte ON pt.Id = rcte.Id
SET pt.PreviousText = rcte.NewPreviousText
WHERE pt.State = 'released';

要是草稿记录的PreviousText也需要改成指向最近的已发布记录，可以单独处理：

UPDATE ProfileText pt
SET PreviousText = (
    SELECT Id FROM ProfileText 
    WHERE State = 'released' AND Id < pt.Id 
    ORDER BY Id DESC LIMIT 1
)
WHERE pt.State = 'draft' AND PreviousText IS NOT NULL;

现在要拉出完整的已发布时间线，可以用递归查询实现：

WITH RECURSIVE ReleasedTimeline AS (
    -- 从最新的已发布记录开始
    SELECT Id, Text, State, PreviousText
    FROM ProfileText
    WHERE State = 'released'
    ORDER BY Id DESC LIMIT 1
    UNION ALL
    -- 递归向上找所有前驱的已发布记录
    SELECT pt.Id, pt.Text, pt.State, pt.PreviousText
    FROM ProfileText pt
    JOIN ReleasedTimeline rt ON pt.Id = rt.PreviousText
)
SELECT * FROM ReleasedTimeline ORDER BY Id ASC; -- 按时间顺序从旧到新展示

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