如何编写SQL筛选具有连续StateFrom与StateTo的有效数据？

第一步：清理已删除记录 #

先过滤掉MH_ID和OP_ID均为空的已删除条目，用公共表表达式（CTE）提取所有有效记录：

WITH valid_records AS (
    SELECT *
    FROM your_table_name
    WHERE NOT (MH_ID IS NULL AND OP_ID IS NULL)
)

第二步：筛选状态连续匹配的有效行 #

保留那些StateFrom能在有效记录的StateTo中找到对应值的行，自动排除因删除导致StateFrom无来源的无效行（比如你提到的ID4）：

写法一：用EXISTS子查询 #

WITH valid_records AS (
    SELECT *
    FROM your_table_name
    WHERE NOT (MH_ID IS NULL AND OP_ID IS NULL)
)
SELECT vr.*
FROM valid_records vr
WHERE EXISTS (
    SELECT 1
    FROM valid_records vr2
    WHERE vr2.StateTo = vr.StateFrom
)
-- 若需保留无前置状态的初始行，可追加：OR vr.StateFrom = '你的初始状态值'

写法二：用IN子查询 #

WITH valid_records AS (
    SELECT *
    FROM your_table_name
    WHERE NOT (MH_ID IS NULL AND OP_ID IS NULL)
)
SELECT *
FROM valid_records
WHERE StateFrom IN (SELECT StateTo FROM valid_records)
-- 若需保留初始行，同样追加：OR StateFrom = '你的初始状态值'

注意事项 #

• 替换your_table_name为实际数据表名
• 如果业务允许存在无前置状态的起始行，自行补充对应判断条件即可

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