时间戳

首页 > 解决方案 > 从 X 中选择 XA 和 XB，然后从 Y 开始计算所有行，其中 YA = XA 且 YB > XB

sql - 从 X 中选择 XA 和 XB，然后从 Y 开始计算所有行，其中 YA = XA 且 YB > XB

问题描述

我有三个表：对话、参与者和消息：

+---------------+
| conversations |
+---------------+
| id            |
+---------------+

+-----------------------------------------------------+
|                     participants                    |
+-----------------+----------------+------------------+
| conversation_id | participant_id |     last_read    |  // "participant_id" is the id of the user.
+-----------------+----------------+------------------+

+---------------------------------------------------------------+
|                            messages                           |
+----+-----------------+-----------+------------------+---------+
| id | conversation_id | sender_id |   sending_time   | content |
+----+-----------------+-----------+------------------+---------+

SQL Fiddle 上的示例数据

我的最终目标是计算用户未阅读的消息总数。如果消息sending_time大于会话last_read值，则用户没有阅读该消息。

示例（使用示例数据）：假设我要计算 user 的未读消息数2。正如我们在示例数据中看到的，在对话中1他收到了来自用户的 2 条消息1（messages1和3），在对话中2他收到了来自用户的 1 条消息1（message 4）。由于用户2上次阅读对话1是在 10:30，因此对话中有 1 条未读消息1(message 3)，由于他还没有阅读对话2，因此对话中有 1 条未读消息，所以我们在所有对话中2总共有2条未读消息，这应该是结果。

我的第一次尝试是处理这部分 SQL 部分代码。所以首先我从我的 C# 程序中执行了以下查询，它获取了用户参与的所有对话：

SELECT conversation_id, last_read
FROM participants
WHERE participant_id = {user_id}

然后，对于上一个查询的每个结果，我执行以下查询：

SELECT COUNT(*)
FROM messages
WHERE
    conversation_id = {result.conversation_id}
    AND
    sender_id <> {user_id}
    AND
    sending_time > {result.last_read}

最后，我以编程方式总结了所有COUNTs 的结果。所有这些工作都很好。

但是，由于许多原因，我需要将此代码重构为 100% SQL，我不确定如何完成此操作。我认为这个过程应该沿着标题中的“查询”的路线。我怎样才能做到这一点？

谢谢。

标签： sqlsql-server

解决方案

我认为这只是一个join适当的过滤器：

select count(*)
from messages m join
     participants p
     on p.conversation_id = m.conversation_id 
where p.participant_id = {user_id} and
      p.last_read < m.sending_time;

推荐阅读