SQL查询筛选同时含F类和M类Offense l_d的Book-in No.记录需求

问题原因说明 #

你在WHERE子句中添加的(trd.l_d like 'F%' AND trd.l_d like 'M%')属于行级过滤条件，单条记录的l_d字段不可能同时以F和M两个不同字符开头，所以查询必然返回0条结果。你需要的是按bookinno分组后，检查整组内是否同时存在两类前缀的记录，属于分组级别的过滤逻辑。

正确实现方案 #

我们可以先通过分组聚合找出所有同时存在F、M前缀l_d的bookinno列表，再将该列表作为过滤条件关联到你的原有查询中，示例代码如下：

SELECT DISTINCT
    CONVERT(varchar, b.bookindt, 101) AS [book-in date],
    b.bookinno AS [book-in no.],
    dbo.fn_getoffensedesc(o.offenseid, o.probviolation,
                          (select offense from trdcode61 
                           where code61id = o.code61id), o.goc) AS offensedescription,
    o.PrimaryOffense AS [Primary Offense],
    trd.l_d AS [offense l/d],
    p.firstName AS [first name],
    p.lastName AS [last name]
FROM
    tblpeople p
LEFT OUTER JOIN 
    tbloffense o (NOLOCK) ON o.personid = p.personid 
LEFT OUTER JOIN 
    tblbookin b (NOLOCK) ON b.bookinid = o.bookinid 
LEFT OUTER JOIN 
    trdcode61 trd (NOLOCK) ON trd.code61id = o.code61id 
WHERE
    dbo.fn_isinjailbybookinid(b.bookinid) = 1 
    AND (trd.l_d LIKE 'F%' OR trd.l_d LIKE 'M%')
    -- 新增过滤条件：仅保留同时存在F、M前缀l_d的bookinno
    AND b.bookinno IN (
        SELECT b_inner.bookinno
        FROM tblbookin b_inner
        LEFT JOIN tbloffense o_inner (NOLOCK) ON o_inner.bookinid = b_inner.bookinid
        LEFT JOIN trdcode61 trd_inner (NOLOCK) ON trd_inner.code61id = o_inner.code61id
        WHERE trd_inner.l_d LIKE 'F%' OR trd_inner.l_d LIKE 'M%'
        GROUP BY b_inner.bookinno
        HAVING COUNT(DISTINCT CASE WHEN trd_inner.l_d LIKE 'F%' THEN 1 WHEN trd_inner.l_d LIKE 'M%' THEN 2 END) = 2
    )
ORDER BY
    p.lastname, p.firstname 

方案说明 #

子查询部分按bookinno分组后，通过HAVING条件判断分组内是否同时存在F、M两类前缀的l_d记录：

• 当l_d以F开头时标记为1，以M开头时标记为2
• 统计分组内不同标记的数量，等于2就说明两类记录都存在

如果你使用的是SQL Server 2012及以上版本，也可以用窗口函数替代子查询实现，写法会更简洁：

WITH base_data AS (
    SELECT DISTINCT
        CONVERT(varchar, b.bookindt, 101) AS [book-in date],
        b.bookinno AS [book-in no.],
        dbo.fn_getoffensedesc(o.offenseid, o.probviolation,
                              (select offense from trdcode61 
                               where code61id = o.code61id), o.goc) AS offensedescription,
        o.PrimaryOffense AS [Primary Offense],
        trd.l_d AS [offense l/d],
        p.firstname AS [first name],
        p.lastname AS [last name],
        -- 统计每个bookinno下的前缀类型数量
        COUNT(DISTINCT CASE WHEN trd.l_d LIKE 'F%' THEN 1 WHEN trd.l_d LIKE 'M%' THEN 2 END) OVER(PARTITION BY b.bookinno) AS type_count
    FROM
        tblpeople p
    LEFT OUTER JOIN 
        tbloffense o (NOLOCK) ON o.personid = p.personid 
    LEFT OUTER JOIN 
        tblbookin b (NOLOCK) ON b.bookinid = o.bookinid 
    LEFT OUTER JOIN 
        trdcode61 trd (NOLOCK) ON trd.code61id = o.code61id 
    WHERE
        dbo.fn_isinjailbybookinid(b.bookinid) = 1 
        AND (trd.l_d LIKE 'F%' OR trd.l_d LIKE 'M%')
)
SELECT * FROM base_data 
WHERE type_count = 2
ORDER BY lastname, firstname

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