聊天室相关SQL查询优化及结果空行移除方法咨询

1. 两个SQL查询的优化方案 #

优化用户所属房间查询 #

原查询用LEFT JOIN但后续通过WHERE过滤出了存在当前用户的房间，改用INNER JOIN逻辑更准确，还能简化语句：

SELECT r.id, r.name
FROM rooms r
INNER JOIN room_participants rp ON r.id = rp.room_id
WHERE rp."participantType" = 'USER' AND rp.participant_id = 1;

如果要避免同一用户重复加入同一房间导致的重复结果，可追加DISTINCT：

SELECT DISTINCT r.id, r.name
FROM rooms r
INNER JOIN room_participants rp ON r.id = rp.room_id
WHERE rp."participantType" = 'USER' AND rp.participant_id = 1;

优化房间内其他成员查询 #

原查询大量嵌套子查询，会重复查询users和shops表，性能浪费严重。改用LEFT JOIN关联两张表，一次性获取所需字段，逻辑更清晰且性能更好：

SELECT
    rp.room_id,
    COALESCE(u.id, s.id) AS participant_id,
    COALESCE(u.first_name, s.name) AS participant_name,
    COALESCE(u.image, s.logo) AS participant_image,
    COALESCE(u.image_src, s.logo_src) AS participant_image_src
FROM room_participants rp
LEFT JOIN users u ON rp."participantType" = 'USER' AND rp.participant_id = u.id AND rp.participant_id != 1
LEFT JOIN shops s ON rp."participantType" = 'SHOP' AND rp.participant_id = s.id
WHERE rp.room_id = 1;

这里用COALESCE函数替代多组CASE语句，优先取用户表字段，不存在则取店铺表字段；同时在JOIN条件里直接排除当前用户，减少后续过滤逻辑。

2. 移除第二个查询结果中的空行 #

如果暂时无法优化查询，只需在原查询末尾添加过滤条件即可：

方式一：过滤空值结果 #

SELECT
    room_id,
    case
        when "participantType" = 'USER' and participant_id != 1 then (SELECT id FROM users WHERE id = participant_id)
        when "participantType" = 'SHOP' then (SELECT id FROM shops WHERE id = participant_id)
    END AS participant_id,

    case
        when "participantType" = 'USER' and participant_id != 1 then (SELECT first_name FROM users WHERE id = participant_id)
        when "participantType" = 'SHOP' then (SELECT name FROM shops WHERE id = participant_id)
    END AS participant_name,

    case
        when "participantType" = 'USER' and participant_id != 1 then (SELECT image FROM users WHERE id = participant_id)
        when "participantType" = 'SHOP' then (SELECT logo FROM shops WHERE id = participant_id)
    END AS participant_image,

    case
        when "participantType" = 'USER' and participant_id != 1 then (SELECT image_src FROM users WHERE id = participant_id)
        when "participantType" = 'SHOP' then (SELECT logo_src FROM shops WHERE id = participant_id)
    END AS participant_image_src

from room_participants

where room_id in (1)
AND participant_id IS NOT NULL; -- 新增过滤空行条件

方式二：直接排除当前用户记录 #

空行本质是当前用户自己的记录，可直接在WHERE里排除，逻辑更直接：

SELECT
    room_id,
    case
        when "participantType" = 'USER' and participant_id != 1 then (SELECT id FROM users WHERE id = participant_id)
        when "participantType" = 'SHOP' then (SELECT id FROM shops WHERE id = participant_id)
    END AS participant_id,

    case
        when "participantType" = 'USER' and participant_id != 1 then (SELECT first_name FROM users WHERE id = participant_id)
        when "participantType" = 'SHOP' then (SELECT name FROM shops WHERE id = participant_id)
    END AS participant_name,

    case
        when "participantType" = 'USER' and participant_id != 1 then (SELECT image FROM users WHERE id = participant_id)
        when "participantType" = 'SHOP' then (SELECT logo FROM shops WHERE id = participant_id)
    END AS participant_image,

    case
        when "participantType" = 'USER' and participant_id != 1 then (SELECT image_src FROM users WHERE id = participant_id)
        when "participantType" = 'SHOP' then (SELECT logo_src FROM shops WHERE id = participant_id)
    END AS participant_image_src

from room_participants

where room_id in (1)
AND NOT ("participantType" = 'USER' AND participant_id = 1); -- 直接排除当前用户

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