mysql - 如何计算特定行之后的行数？

Question

我的网站用户之间有一个联系机制(总是在两个用户之间)。用户 A(发送者)可以根据两个规则向用户 B(接收者)发送消息:

1. 用户 A 最多可以向用户 B 发送 2 条消息，并且他必须至少从用户 B 收到一条消息才能发送另外 2 条消息。

2. 用户A每天最多可以给每个人发送4条消息，不能更多。

这是我的表结构:

-- contact
+----+-----------+------------+--------------------------+------------+
| id | sender_id | receive_id |          message         |  date_time |
+----+-----------+------------+--------------------------+------------+
| 1  | 123       | 456        | Hi, how are you?         | 1492431111 |
| 2  | 123       | 789        | How are you doing?       | 1492431112 |
| 3  | 456       | 789        | Why would you say that?  | 1492431113 |
| 4  | 123       | 456        | Why don't you answer?    | 1492431114 |
| 5  | 789       | 456        | Because the sky is high  | 1492431115 |
| 6  | 123       | 789        | Hello?                   | 1492431116 | 
+----+-----------+------------+--------------------------+------------+

这是我当前的查询:

INSERT INTO contact(sender_id, receive_id, message, date_time )
SELECT ?, ?, ?, unix_timestamp()
FROM dual
WHERE NOT EXISTS(
    SELECT count(*) AS num_day,
    FROM contact
    WHERE user_id = ?
      AND date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 day))
    HAVING num_day > 4
) 

如您所见，我的查询中只实现了第二条规则。我还如何实现查询的第一条规则？

Answer 1

查询以找出自上次回复以来从“A”到“B”的消息数:

SELECT count(*)
FROM contact
WHERE sender_id = 'A'
  AND receive_id = 'B'
  AND date_time > (
    SELECT max(date_time)
    FROM contact
    WHERE sender_id = 'B'
      AND receive_id = 'A'
    )

请注意，如果还没有从“B”到“A”的消息，这可能不起作用 - 您必须修改查询以说明这一点。