SQL子查询返回多行报错：如何正确查询上月数据？

错误原因 #

你遇到的"More than one row returned by a subquery used as an expression"错误，是因为CASE语句中的子查询返回了多条记录——当某个就诊ID（visit_id）在digital_signature_images或patient_images表中存在多条标题为IMPORTANT LETTER FROM MEDICARE的记录时，子查询会返回多个值，而CASE表达式要求子查询只能返回单个值。同时原SQL的INNER JOIN会过滤掉没有对应图片记录的就诊，导致结果不完整。

修改后的SQL语句 #

SELECT
    v.visit_id,
    v.visit_name,
    v.visit_admit_date,
    v.visit_disch_date,
    v.visit_stay_type,
    v.visit_ins,
    ipv1.ipv1_room,
    ipv1.ipv1_ad_init,
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM public.digital_signature_images dsi
            WHERE dsi.dsigimg_acct = v.visit_id
              AND dsi.dsigimg_title = 'IMPORTANT LETTER FROM MEDICARE'
        ) THEN 'YES'
        WHEN EXISTS (
            SELECT 1
            FROM public.patient_images pi
            WHERE pi.patimg_acct = v.visit_id
              AND pi.patimg_title = 'IMPORTANT LETTER FROM MEDICARE'
        ) THEN 'YES'
        ELSE 'NOT ON FILE'
    END AS IMFM_ON_FILE
FROM public.visit v
INNER JOIN public.ip_visit_1 ipv1 ON v.visit_id = ipv1.ipv1_num
LEFT JOIN public.digital_signature_images dsi ON v.visit_id = dsi.dsigimg_acct
LEFT JOIN public.patient_images pi ON v.visit_id = pi.patimg_acct
WHERE
    v.visit_ins LIKE 'M%'
    AND v.visit_stay_type = '1'
    AND ipv1.ipv1_dis_date >= DATE_TRUNC('MONTH', CURRENT_DATE - INTERVAL '1 MONTH')
    AND ipv1.ipv1_dis_date < DATE_TRUNC('MONTH', CURRENT_DATE)
GROUP BY
    v.visit_id,
    v.visit_name,
    v.visit_admit_date,
    v.visit_disch_date,
    v.visit_stay_type,
    v.visit_ins,
    ipv1.ipv1_room,
    ipv1.ipv1_ad_init
ORDER BY ipv1.ipv1_room;

关键修改点 #

• 用EXISTS替代字段查询：EXISTS仅判断是否存在符合条件的记录，不会返回多行，彻底解决子查询的报错问题，同时性能更优
• 替换INNER JOIN为LEFT JOIN：保留所有符合就诊条件的记录，避免过滤掉没有对应图片的就诊
• 使用表别名简化代码：提升SQL的可读性和维护性
• 用GROUP BY去重：替代原有的DISTINCT，更清晰地控制分组逻辑，避免LEFT JOIN带来的重复行

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