mysql - 如何为 DISTINCT 使用多列？

Question

我正在制作一个消息系统。消息是两种不同的类型。

• 第一条消息有一个 title 和 related 列的 NULL。
• 第二条消息与没有 title 的第一条消息相关，它们具有 related 列的父消息。

这是我的表结构:

// 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  |          | whatever6        | 5         | 1           | 1521097245  | 5       |
| 7  |          | whatever7        | 4         | 1           | 1521097246  | 4       |
| 8  | title4   | whatever8        | 1         | 4           | 1521097246  | NULL    |
+----+----------+------------------+-----------+-------------+-------------+---------+
/*
  related column: it is NULL for the first message and the id of the parent for othesrs.

现在我需要统计一个用户在一天内发送给不同用户的消息数和不同的新消息数。

因此对于 sender_id = 1，预期结果是 4。这是我当前的查询，它返回 3:

SELECT count(distinct receiver_id) as sent_messages_num
FROM users
WHERE sender_id = 1  
AND date_time > UNIX_TIMESTAMP(DATE_SUB(now(), INTERVAL 1 DAY))

我当前的查询不关心“新消息”。它仅根据不同的用户对消息进行分组。我怎样才能添加“即使新消息发送给同一用户也应该计算在内”的概念？

Answer 1

尝试下面的查询

SELECT sum(sent_messages_num)
FROM
(
    SELECT count(distinct receiver_id) as sent_messages_num
    FROM users
    WHERE sender_id = 1  
    AND date_time > UNIX_TIMESTAMP(DATE_SUB(now(), INTERVAL 1 DAY))
    GROUP BY CASE WHEN related IS NULL THEN id ELSE related END
) t

dbfiddle demo