select * WHERE status <> 'deleted‘或status <> 'completed’AND update_datetime < unix_timestamp(now() - interval 7 day)

Question

如果update_datetime列的时间超过7天(MySQL version 2013-04-11 11:22:44 )，我想拉出状态列未标记为“已删除”且状态标记为“已完成”的所有行。

这是我失败的尝试：

WHERE
    status <> 'deleted'
 OR status <> 'completed'
AND update_datetime < unix_timestamp( now() - interval 7 day )

Answer 1

WHERE
    status <> 'deleted'
AND (
        (update_datetime < unix_timestamp( now() - interval 7 day ) AND status = 'completed')
    OR  (update_datetime >= unix_timestamp( now() - interval 7 day ))
)

如果update_datetime是DATETIME列，请改用以下内容：

WHERE
    status <> 'deleted'
AND (
        (update_datetime < DATE_SUB(NOW(), INTERVAL 7 day) AND status = 'completed')
    OR  (update_datetime >= DATE_SUB(NOW(), INTERVAL 7 day))
)

Answer 2

你是说这个吗？

WHERE
    (status <> 'deleted'
 OR status <> 'completed')
AND update_datetime < unix_timestamp( now() - interval 7 day )

它等于

WHERE
    (not status in ('deleted', 'completed'))
AND update_datetime < unix_timestamp( now() - interval 7 day )