php - mysql-查询其他表条件PHP

Question

我在 mysql 中有一张名为 safespot 的表

+---------+---------------+
| term_id | userid | safe |
+---------+--------|------+
|       1 | 1      |  large number, unix timestamp here
|       1 | 2      | large number, unix timestamp here
|       1 | 3      | large number, unix timestamp here
|       1 | 4      | large number, unix timestamp here
+---------+--------+

这是表 users:

+----+-------------+-------------+
| id |    userid   |    cash     |
+----+-------------+-------------+
|  1 |     1       |    100000   |
|  2 |     2       |    100000   |
|  3 |     3       |    100000   |
+----+-------------+-------------+

我该怎么做

SELECT * FROM `users` where `userid`=1 and `cash`>= 1000 and " userid do not exist in table safespot" or "if the user exists in the safestop table, check if the current timestamp is higher than the safe colum)

所以基本上做一个查询，如果 safespot 表中不存在 userid 也会返回它，或者如果存在，则时间戳高于 safe_value。

Answer 1

SELECT * FROM users u
LEFT JOIN safespot s ON s.userid = u.userid 
WHERE
    u.userid = 1
    AND u.cash = 1000
    AND (s.userid IS NULL OR s.safe > UNIX_TIMESTAMP())

这会返回用户所在的位置

• safespot 中没有给定用户 ID 的条目，或者
• safespot 中有一个条目，其 safe 的值大于当前时间戳。