时间戳

首页 > 解决方案 > 即使连接表有空记录，Postgresql 也会选择计数

sql - 即使连接表有空记录，Postgresql 也会选择计数

问题描述

我有一个用户表如下

表用户

{id:1, name:'USER 1'}
{id:2, name:'USER 2'}
{id:3, name:'USER 3'}
{id:4, name:'USER 4'}

USER 1 向所有用户发送消息，聊天窗口 id 插入 table.messages

表消息

{id:1,chat_id:1, from_user:1: message:'Hi'}
{id:2,chat_id:1, from_user:1: message:'How are you'}
{id:3,chat_id:1, from_user:1: message:'This is broadcasted'}

table.last_read

{id:1, last_read_message_id:3, user_id:2, chat_id:1}
{id:2, last_read_message_id:3, user_id:3, chat_id:1}

我正在使用下面的查询来获取用户的未读消息，因为 table.last_read 仅在至少一个读取时插入，USER 4 不会在 table.last_read 中记录，下面在左连接中返回 last_read_message_id null 并返回 0记录

   SELECT COUNT(*) FROM messages  m
    LEFT JOIN (
        SELECT last_read_message_id
        FROM last_read  
        WHERE user_id=4
    ) lr ON m.chat_id=lr.chat_id
    WHERE m.chat_id=1 AND m.id>last_read_message_id

标签： sqlpostgresql

解决方案

的使用LEFT JOIN被否定，因为您在WHERE子句中引用了该表中的字段。它的行为就像INNER JOIN一个结果。

要解决此问题，请将标准添加到您的JOIN规范中。

SELECT COUNT(*)
FROM messages m
LEFT JOIN last_read lr
  ON  m.chat_id = lr.chat_id
  AND m.id > lr.last_read_message_id
  AND lr.user_id = 4
WHERE m.chat_id = 1

这相当于你的 last_read_message_id 从 移动WHERE到JOIN.

小提琴：http ://sqlfiddle.com/#!17/c0b0f/2

推荐阅读