SQL查询求助：按截取后的Ref分组筛选含I1且无H4的记录

我有如下数据表，需要按Ref字段截取前9位进行分组，筛选出分组内存在MessageType为'I1'但不存在MessageType为'H4'的记录。Ref字段有时末尾会带有/001，因此需要在SELECT和分组时使用SUBSTRING函数。下方示例表中，仅截取后的REF2_ABCD符合条件——它存在MessageType为I1的记录，但不存在MessageType为H4的记录。

我尝试编写的SQL代码如下：

SELECT
SUBSTRING(Ref,1,9) AS LRN,
MessageType
FROM table1 dh
WHERE MessageType IN ('I1', 'H4')
GROUP BY SUBSTRING(Ref,1,9),MessageType
HAVING MessageType = 'I1' AND NOT MessageType = 'H4'

问题分析 #

你当前的SQL逻辑存在问题：GROUP BY同时包含了截取后的Ref（LRN）和MessageType，这会把每个LRN+MessageType的组合单独成组，HAVING条件只能判断当前组的MessageType，无法跨组检查同一个LRN下是否存在H4记录，所以达不到预期效果。

正确实现方案 #

这里提供两种可行的写法：

方法1：条件聚合统计

SELECT
    SUBSTRING(Ref, 1, 9) AS LRN
FROM table1 dh
WHERE MessageType IN ('I1', 'H4')
GROUP BY SUBSTRING(Ref, 1, 9)
HAVING 
    SUM(CASE WHEN MessageType = 'I1' THEN 1 ELSE 0 END) > 0
    AND SUM(CASE WHEN MessageType = 'H4' THEN 1 ELSE 0 END) = 0;

这种方法通过分组后的条件聚合，统计每个LRN下I1和H4的记录数，直接筛选出有I1但无H4的分组。

方法2：NOT EXISTS子查询

SELECT DISTINCT
    SUBSTRING(Ref, 1, 9) AS LRN
FROM table1 t1
WHERE 
    t1.MessageType = 'I1'
    AND NOT EXISTS (
        SELECT 1 
        FROM table1 t2
        WHERE SUBSTRING(t2.Ref, 1, 9) = SUBSTRING(t1.Ref, 1, 9)
        AND t2.MessageType = 'H4'
    );

这种方法先定位所有包含I1的记录，再通过子查询排除掉那些同LRN下存在H4的记录，最后用DISTINCT去重得到唯一的LRN。

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