带 Date_Sub 的 MySQL 查询在查询时返回空集，但包含连接时计数 = 1

Question

我正在尝试获取我网站上最后活跃聊天用户的数据集，但是查询中的 where 子句“失败”并返回“已过期”的结果...

以下是我所期望的......

mysql> select * from openchats where date_sub(now(), interval 15 minute) < active;
Empty set (0.00 sec)

但是，我连接了几个表以使我的生活更轻松，当我期望计数为零时，连接查询返回计数为 1...

mysql> SELECT
    ->   (
    ->     COUNT(*)
    ->   ) AS `count`
    -> FROM
    ->   openchats Openchats
    ->   LEFT JOIN users Users ON Users.id = (Openchats.user_id)
    ->   LEFT JOIN chatrooms Chatrooms ON Chatrooms.id = (Openchats.chatroom_id)
    -> WHERE
    ->   (
    ->     DATE_SUB(NOW(), interval 15 minute) < 'Openchats.active'
    ->     AND open = 1
    ->   );
+-------+
| count |
+-------+
|     1 |
+-------+
1 row in set, 1 warning (0.00 sec)

这是表格的数据

mysql> select * from openchats;
+----+-------------+------+---------------------+--------------------------------------+---------------------+---------------------+
| id | chatroom_id | open | active              | user_id                              | created             | modified            |
+----+-------------+------+---------------------+--------------------------------------+---------------------+---------------------+
|  1 |           3 |    1 | 2017-07-31 19:14:33 | 3f189dab-597a-468f-8f6f-ae577a8e05e8 | 2017-07-31 18:38:00 | 2017-07-31 18:38:00 |
+----+-------------+------+---------------------+--------------------------------------+---------------------+---------------------+
1 row in set (0.00 sec)

任何人都可以向我解释如何使第二个查询返回计数为零吗？

谢谢...

Answer 1

您的错误似乎在以下行中:

DATE_SUB(NOW(), interval 15 minute) < 'Openchats.active'

您正在将 DATE_SUB 的结果与字符串“Openchats.active”进行比较，而不是与 Openchats 表的事件属性进行比较。尝试删除引号并再次运行:

DATE_SUB(NOW(), interval 15 minute) < Openchats.active