SQL Server多表联查未按预期过滤数据问题求助

我有两个SQL Server表，联查后无法得到预期结果。表OECMTHST_SQL中存在多条real_ord_no相同但ord_no不同的记录。表OEHDRHST_SQL的ord_no对应多条记录，其invoice_no字段与OECMTHST_SQL的ord_no字段相同。

需求 #

筛选出不存在cmt='PA_INV:SUCCESSFULLY SENT'（该值固定出现在cmt_seq_no=4的行中）的ord_no，预期结果为214738和214739。

尝试的查询 #

Query #1 #

SELECT
    O.ord_no
FROM
    OECMTHST_SQL O
INNER JOIN
    oehdrhst_sql H ON LTRIM(RTRIM(O.real_ord_no)) = LTRIM(RTRIM(H.ord_no))
WHERE
    O.cmt NOT LIKE 'PA INV%%'
    AND H.ord_type = 'O'
    AND O.cmt LIKE 'Inv_Email%%'
    AND H.entered_dt > '2022-09-29 00:00:00.000'
GROUP BY
    o.ord_no
HAVING
    SUM(cmt_seq_no) < 10
ORDER BY
    O.ord_no

Query #2 #

SELECT
    O.ord_no,
    SUM(O.cmt_seq_no)
FROM
    OECMTHST_SQL O 
INNER JOIN
    oehdrhst_sql H ON LTRIM(RTRIM(O.real_ord_no)) = LTRIM(RTRIM(H.ord_no))
WHERE
    H.ord_type = 'O'
    AND H.entered_dt > CONVERT(VARCHAR(10), GETDATE()-1, 101)
GROUP BY
    O.ord_no
HAVING
    SUM(O.cmt_seq_no) < 10

问题分析与正确解法 #

你的两个查询逻辑存在偏差：

1. Query #1通过O.cmt NOT LIKE 'PA INV%%'过滤行，会直接排除包含该内容的行，但同一ord_no可能还有其他行，导致分组后无法准确判断是否存在目标记录；
2. 两个查询用SUM(cmt_seq_no) <10来判断是否缺少cmt_seq_no=4的行，逻辑不严谨——如果其他seq_no的和刚好凑够≥10，或者存在其他seq_no组合，都会导致误判。

正确思路是：先找出所有存在cmt='PA_INV:SUCCESSFULLY SENT'且cmt_seq_no=4的ord_no，再筛选出不在这个集合里的目标ord_no，同时结合OEHDRHST_SQL的过滤条件。

正确查询语句（NOT IN版本） #

SELECT DISTINCT O.ord_no
FROM OECMTHST_SQL O
INNER JOIN oehdrhst_sql H 
    ON LTRIM(RTRIM(O.real_ord_no)) = LTRIM(RTRIM(H.ord_no))
WHERE 
    H.ord_type = 'O'
    AND H.entered_dt > '2022-09-29 00:00:00.000'
    AND O.ord_no NOT IN (
        SELECT ord_no
        FROM OECMTHST_SQL
        WHERE cmt = 'PA_INV:SUCCESSFULLY SENT' 
          AND cmt_seq_no = 4
    )
ORDER BY O.ord_no;

正确查询语句（NOT EXISTS版本，大数据量下性能更优） #

SELECT DISTINCT O.ord_no
FROM OECMTHST_SQL O
INNER JOIN oehdrhst_sql H 
    ON LTRIM(RTRIM(O.real_ord_no)) = LTRIM(RTRIM(H.ord_no))
WHERE 
    H.ord_type = 'O'
    AND H.entered_dt > '2022-09-29 00:00:00.000'
    AND NOT EXISTS (
        SELECT 1
        FROM OECMTHST_SQL sub
        WHERE sub.ord_no = O.ord_no
          AND sub.cmt = 'PA_INV:SUCCESSFULLY SENT' 
          AND sub.cmt_seq_no = 4
    )
ORDER BY O.ord_no;

这两个查询都会准确筛选出不存在指定记录的ord_no，符合预期结果。

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