我正在尝试获取我网站上最后一个活跃聊天用户的数据集,但是查询中的 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)
谁能向我解释如何使第二个查询返回零计数?
谢谢。。。
看起来你的错误在以下行:
DATE_SUB(NOW(), interval 15 minute) < 'Openchats.active'
您正在将DATE_SUB的结果与字符串"Openchats.active"进行比较,而不是与Openchats表的活动属性进行比较。尝试删除引号并再次运行它:
DATE_SUB(NOW(), interval 15 minute) < Openchats.active