MySQL临时表查询-将计算列添加到select中计算的表中

Question

我正在尝试从查询中创建一个临时表，其中包含在查询中计算的另一列...

这是我的查询:

asprintf(&query,
"CREATE TEMPORARY TABLE IF NOT EXISTS task_tab (PRIMARY KEY(event_id))"
"SELECT V.id as event_id, V.event_time, D.user, V.location FROM device D "
"JOIN device_service DS ON D.id = DS.device_id "
"JOIN services S ON DS.service_id = S.id "
"JOIN device_event V ON D.id = V.device_id "
"WHERE V.store = 'event_box' AND S.options = 'box_length' AND (S.flags & 1 = 1)"
"AND V.event_time + (IF(S.value IS NULL, %d, S.value) * 86400000) <= %llu"
"AND D.id IN ( SELECT D.id FROM device D "
    "JOIN device_service DS ON D.id = DS.device_id "
    "JOIN services S ON DS.service_id = S.id "
    "WHERE S.action = 'delete' AND (S.flags & 1 = 1)",
app_config->def_expire, current_epoch_ms);

我想在这个临时表中创建一个列“expire_time”，并将这部分查询的结果存储在该列中:

V.event_time + (IF(S.value IS NULL, %d, S.value) * 86400000)

有什么想法吗？

Answer 1

和

V.event_time + (IF(S.value IS NULL, %d, S.value) * 86400000) AS `expire_time`

到您选择的字段列表