基于临时表关联验证文档有效性的SQL查询编写求助

大家好，我现在卡在一个SQL查询的需求上，想请各位大佬帮忙指点下。先跟大家说下我的业务场景：

我有两个临时表，表A（@Segment_Table）是从输入文档里提取的段数据，每一行代表文档里的一个段，包含line_no（行号，整数型）和segment（段标识，字符串型）字段。规则是ST段标志着一个新文档的开始，每个ST后面紧跟的那个段就是这个文档的起始段。

表A的具体数据如下：

然后是表B（@BCH_Segment_Table），存储的是合法的文档起始段与文档名的对应关系，字段是Document_name（文档名，字符串型）和segment（合法起始段，字符串型）。

表B的具体数据如下：

我的需求是：找出每个以ST开头的文档，验证它紧跟在ST后面的起始段是否在表B的segment字段中，如果存在则标记为Valid，否则标记为Invalid。最终要输出4个文档的验证结果（每个ST对应一个文档），而不是每个段的结果。

我自己写了一段查询，但完全达不到预期效果：

SELECT
a.Segment_Identifier,
CASE WHEN EXISTS (SELECT * FROM @BCH_Segment_Table AS b WHERE b.beg_segment = a.Segment_Identifier)
THEN 'Valid'
ELSE 'Invalid'
END AS NewFiled
FROM @Segment_Table as a

期望的输出应该是：

• document 1 : valid
• document 2 : valid
• document 3 : valid
• document 4 : invalid

各位能不能帮我写出正确的SQL查询？

解决方案 #

下面提供两种常用的写法，适配不同的SQL方言场景：

方法1：使用LEAD窗口函数（适合支持窗口函数的数据库，如SQL Server、PostgreSQL、MySQL 8.0+等）

利用窗口函数LEAD可以直接获取当前行的下一行数据，我们先筛选出所有ST行，获取它们的下一个段作为文档起始段，再关联表B验证合法性，同时给每个文档编号：

WITH document_starts AS (
    SELECT
        -- 获取ST段的下一个段，即当前文档的起始段
        LEAD(segment) OVER(ORDER BY line_no) AS beg_segment,
        -- 按ST出现的顺序给文档编号
        ROW_NUMBER() OVER(ORDER BY line_no) AS document_number
    FROM @Segment_Table
    WHERE segment = 'ST'
)
SELECT
    CONCAT('document ', document_number) AS document_name,
    CASE 
        WHEN EXISTS (SELECT 1 FROM @BCH_Segment_Table b WHERE b.segment = ds.beg_segment)
        THEN 'valid'
        ELSE 'invalid'
    END AS validation_result
FROM document_starts ds;

方法2：使用自关联（兼容性更强，适合不支持窗口函数的旧版数据库）

通过自关联找到每个ST行的下一行（行号+1），得到文档起始段后，再关联表B判断是否合法：

WITH document_starts AS (
    SELECT
        next_seg.segment AS beg_segment,
        -- 按ST出现的顺序给文档编号
        ROW_NUMBER() OVER(ORDER BY st.line_no) AS document_number
    FROM @Segment_Table st
    -- 关联ST的下一行
    JOIN @Segment_Table next_seg ON next_seg.line_no = st.line_no + 1
    WHERE st.segment = 'ST'
)
SELECT
    CONCAT('document ', document_number) AS document_name,
    CASE 
        WHEN b.segment IS NOT NULL
        THEN 'valid'
        ELSE 'invalid'
    END AS validation_result
FROM document_starts ds
-- 左连接表B，判断起始段是否合法
LEFT JOIN @BCH_Segment_Table b ON ds.beg_segment = b.segment;

结果说明 #

执行上述任意一种查询，都会得到你期望的输出结果：

备注：内容来源于stack exchange，提问作者Rahul