mysql - 根据先前计算的值插入值

Question

我有下表“tasks_processes”:

id| task_id | process_id | start | end  
 1| 2       | 43         | 0     | 250
 2| 2       | 82         | 250   | 500
 3| 2       | 21         | 500   | 750

因此，“开始”和“结束”列表示范围的边界并且在任务中是唯一的。对于新流程，我需要像这样执行 INSERT SELECT 查询:

INSERT INTO tasks_processes (start,end,task_id,process_id) 
    SELECT 
        COALESCE ((SELECT MAX(end) FROM tasks_processes WHERE task_id=2),0) as new_val , 
        new_val + 250, 
        2, 
        12;

换句话说，我需要根据之前计算的值插入值。但是 MySQL 给我 DatabaseError: no such column: new_val

如何解决这个问题？

Answer 1

你必须像这样重复子查询:

INSERT INTO tasks_processes (start,end,task_id,process_id) 
SELECT COALESCE ((SELECT MAX(end) FROM tasks_processes WHERE task_id=2),0), 
       COALESCE ((SELECT MAX(end) FROM tasks_processes WHERE task_id=2),0) + 250, 
       2, 
       12;

或者你可以使用这样的东西:

INSERT INTO tasks_processes (start,end,task_id,process_id) 
SELECT new_val, new_val+250, 2, 12
FROM (SELECT coalesce(MAX(end), 0) as new_val FROM tasks_processes WHERE task_id=2) s;