如何修正SQL查询，实现判断患者是否收到代理消息的布尔列

首先明确需求：为每个患者添加布尔列requiresReply，当患者收到过非ID为3的代理发送的消息时，该列值为true，否则为false。

问题分析 #

你的原查询存在两个核心问题：

• 子查询内部的SELECT 1 WHERE id IS null逻辑完全偏离需求，这部分和判断代理发送消息无关；
• 当没有匹配到代理发送的消息时，子查询返回null，而非预期的false。

方案一：使用EXISTS子查询（推荐） #

EXISTS会直接返回布尔值，且性能更优，适合判断“是否存在”类场景：

SELECT 
    patient.id AS "id",
    first_name AS "firstName",
    EXISTS (
        SELECT 1 
        FROM message 
        WHERE 
            message.created_by_agent_id IS NOT NULL 
            AND message.created_by_agent_id != 3 
            AND message.to_phone_number = patient.phone_number
    ) AS "requiresReply"
FROM patient

方案二：使用聚合函数+CASE语句 #

如果需要兼容不支持EXISTS直接返回布尔值的数据库，可以用MAX结合CASE来实现：

SELECT 
    patient.id AS "id",
    first_name AS "firstName",
    CASE 
        WHEN MAX(CASE WHEN message.created_by_agent_id IS NOT NULL AND message.created_by_agent_id != 3 THEN 1 ELSE 0 END) = 1 
        THEN true 
        ELSE false 
    END AS "requiresReply"
FROM patient
LEFT JOIN message ON message.to_phone_number = patient.phone_number
GROUP BY patient.id, patient.first_name

关键说明 #

• 两个方案都明确判断created_by_agent_id IS NOT NULL，确保是代理发送的消息（对应你给出的messages表中代理发送的记录created_by_agent_id非空）；
• 保留了原查询中created_by_agent_id !=3的条件，排除ID为3的代理发送的消息；
• 方案一无需分组，逻辑更简洁，执行效率更高；方案二通过分组聚合，确保每个患者只返回一条记录。

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