如何统计去年未回复的邮件?

ws51t4hk  于 2021-06-24  发布在  Mysql
关注(0)|答案(2)|浏览(351)

我在做一个信息系统。信息有两种不同的类型。
第一条消息有一个 title 以及 NULL 为了 related 列。
第二条消息与第一条消息中没有 title 它们具有的父消息id related 列(它们被称为响应(response/reply/answer)
这是我的表格结构:

// messages
+----+----------+------------------+-----------+-------------+-------------+---------+
| id |  title   |     content      | sender_id | receiver_id |  date_time  | related |
+----+----------+------------------+-----------+-------------+-------------+---------+
| 1  | titel1   | whatever1        | 1         | 3           | 1521097240  | NULL    |
| 2  |          | whatever2        | 3         | 1           | 1521097241  | 1       |
| 3  |          | whatever3        | 1         | 3           | 1521097242  | 1       |
| 4  | title2   | whatever4        | 1         | 4           | 1521097243  | NULL    |
| 5  | title3   | whatever5        | 1         | 5           | 1521097244  | NULL    |
| 6  |          | whatever7        | 4         | 1           | 1521097246  | 4       |
| 7  | title4   | whatever8        | 1         | 4           | 1521097246  | NULL    |
+----+----------+------------------+-----------+-------------+-------------+---------+
/*
  related column: it is NULL for the first message and the id of the parent for othesrs.

现在,我需要计算用户a在过去一年中发送但未收到回复的消息数。
例如,用户的消息数 user_id = 1 已发送,但尚未收到is的回复 1 . 因为他给用户发了条信息 user_id = 5 他还没有回应。
我怎么数那个数字?

SELECT count(1)
FROM messages
WHERE sender_id = 1
  AND date_time > UNIX_TIMESTAMP(DATE_SUB(now(), INTERVAL 1 YEAR))

我的查询统计所有发送的消息。我怎么能只数那些没有回答的?

zc0qhyus

zc0qhyus1#

让我假设你真的是指由“a”发送的第一条消息。如果是这样,您的示例查询需要进行筛选 related is NULL . 要过滤非响应,可以使用 LEFT JOIN / WHERE 或者 NOT EXISTS :

SELECT count(*)
FROM messages m LEFT JOIN
     messages m2
     ON m2.related = m.id
WHERE m.sender_id = 1 AND
      m.related IS NULL AND
      m.date_time > UNIX_TIMESTAMP(DATE_SUB(now(), INTERVAL 1 YEAR)) AND
      m2.id IS NULL;  -- response does not exist
yi0zb3m4

yi0zb3m42#

使用 NOT EXISTS ```
SELECT count(1)
FROM messages m1
WHERE sender_id = 1 AND
related IS NULL AND
date_time > UNIX_TIMESTAMP(DATE_SUB(now(), INTERVAL 1 YEAR)) AND
NOT EXISTS (
SELECT 1
FROM messages m2
WHERE m1.id = m2.related
)

相关问题