mysql 相减2行显示在第三行

Question

我有一个mysql查询，它从数据库中拉出创建和修改时间，然后将它们更改为unix时间

SELECT
    tn,
    title,
    UNIX_TIMESTAMP(create_time) AS create_time,
    UNIX_TIMESTAMP(change_time) AS change_time
FROM
    ticket
WHERE
    responsible_user_id = 2
AND ticket_state_id IN (2, 3, 10)
ORDER BY
    change_time DESC

结果如下:

+---------+----------------------+-------------+-------------+
| tn      | title                | create_time | change_time |
+---------+----------------------+-------------+-------------+
| 7409025 | Re: otrs             |  1459861204 |  1459861263 |
| 7408967 | RINAS@cartrack.co.za |  1459765215 |  1459836797 |
| 7408935 | FW: Vod              |  1459767210 |  1459836797 |

如何从 create_time 行中减去 change_time 行，以便这些行在名为 ave 的新行中显示结果。

+---------+----------------------+-------------+-------------+------+
| tn      | title                | create_time | change_time | ave  |
+---------+----------------------+-------------+-------------+------+
| 7409025 | Re: otrs             |  1459861204 |  1459861263 |  59  |
| 7408967 | RINAS@cartrack.co.za |  1459765215 |  1459836797 | 71582|
| 7408935 | FW: Vod              |  1459767210 |  1459836797 | 69587|

Answer 1

你几乎做到了。

SELECT
    tn,
    title,
    UNIX_TIMESTAMP(create_time) AS create_time,
    UNIX_TIMESTAMP(change_time) AS change_time,
    (UNIX_TIMESTAMP(change_time) - UNIX_TIMESTAMP(create_time)) AS ave
FROM
    ticket
WHERE
    responsible_user_id = 2
AND ticket_state_id IN (2, 3, 10)
ORDER BY
    change_time DESC