mysql - 如何对列执行数学运算，然后在 MySQL 的 where 子句中使用该值？

Question

我正在尝试从 modx 数据库获取过去 15 分钟内处于事件状态的用户列表，modx_session 中的访问字段有他们上次访问的 unix 时间。我想要做的就是获取所有访问时间 (UNIX_TIMESTAMP() - us.access) 小于 900 秒的用户:

这是我到目前为止所拥有的:

SELECT 
(UNIX_TIMESTAMP() - us.access) as last_access, u.id, u.username, ua.*, us.*, ux.*, u.id as id, ua.id as uaid, us.id as usid, ux.id as uxid 
FROM `modx_users` u 
LEFT JOIN `modx_user_attributes` ua ON ua.internalKey = u.id
LEFT JOIN `modx_gssi_user_ext_data` ux ON  ux.internalKey = u.id
LEFT JOIN `modx_session` us ON  us.id = ua.sessionid
WHERE ( ua.blocked = 0 AND u.active = 1 AND u.id != 1 AND last_access <= '900') 
ORDER BY u.id asc;

但我收到错误:

[Err] 1054 - Unknown column 'last_access' in 'where clause'

我做错了什么

Answer 1

不必担心使用 UNIX_TIMESTAMP() - us.access 两次，MySQL 的优化器仅运行此表达式一次。

SELECT 
    (UNIX_TIMESTAMP() - us.access) AS last_access,
    u.id,
    u.username,
    ua.*,
    us.*,
    ux.*,
    u.id AS id,
    ua.id AS uaid,
    us.id AS usid,
    ux.id AS uxid
FROM
    `modx_users` u
        LEFT JOIN
    `modx_user_attributes` ua ON ua.internalKey = u.id
        LEFT JOIN
    `modx_gssi_user_ext_data` ux ON ux.internalKey = u.id
        LEFT JOIN
    `modx_session` us ON us.id = ua.sessionid
WHERE
    (ua.blocked = 0 AND u.active = 1
        AND u.id != 1
        AND UNIX_TIMESTAMP() - us.access <= '900')
ORDER BY u.id ASC;