mysql - SQL 查找类似函数

Question

我在 SQL 表中有 5 列

Id  | activity    | start date | Finish date | Predecessor activity 
1   | shuttering  | 1/1/2019   | 3/1/2019    | 
2   | concrete    |            | 6/1/2019    | 1

以上是我的表格的示例我需要计划我的事件并且所有事件都是相互关联的ID 2 接续事件 1因此 ID 2 的开始日期是 1 的结束日期我需要自动生成所有开始日期，除了基于前一事件的第一个开始日期

Answer 1

基于此表

CREATE TABLE Schedule1
(`Id` int, `activity` varchar(10), `start_date` datetime
, `Finish_date` datetime, `Predecessor_activity` int)
ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO Schedule1
(`Id`, `activity`, `start_date`, `Finish_date`, `Predecessor_activity`)
VALUES
(1, 'shuttering', '2019-01-01', '2019-03-01 01:00:00', NULL),
(2, 'concrete', NULL, '2019-06-01 02:00:00', 1),
(3, 'concrete', NULL, '2019-08-01 02:00:00', 2),
(4, 'concrete', NULL, '2019-10-01 02:00:00', 3);

这条sql语句

UPDATE Schedule1 t2
Left JOIN Schedule1 t1 ON t2.Predecessor_activity   = t1.Id
SET t2.start_date = IFNULL(t2.start_date,t1.Finish_date);

让你明白

Id  activity    start_date              Finish_date          Predecessor_activity
1   shuttering  2019-01-01 00:00:00     2019-03-01 01:00:00     
2   concrete    2019-03-01 01:00:00     2019-06-01 02:00:00     1
3   concrete    2019-06-01 02:00:00     2019-08-01 02:00:00     2
4   concrete    2019-08-01 02:00:00     2019-10-01 02:00:00     3

用于使用插入或更新等触发器像这样

#DELIMITER //
DROP TRIGGER IF EXISTS my_update_trigger;
CREATE DEFINER=root@localhost TRIGGER my_update_trigger
AFTER update ON `schedule1`
FOR EACH ROW

BEGIN
-- Call the common procedure ran if there is an INSERT or UPDATE on `table`
-- NEW.id is an example parameter passed to the procedure but is not required
-- if you do not need to pass anything to your procedure.
CALL procedure_to_run_schedule()//
END//

分隔符；

我们需要一个新表，因为触发器不允许更新同一个表。因此，我们创建一个与原始表相同的新表

 DROP TABLE IF EXISTS newschedul;

CREATE TABLE newschedule SElect * From Schedule1;

然后我们添加一个新的存储过程。它的作用是，在插入或更新原始表后，它将所有数据复制到新表并使用上述语句来制定新计划。因此您可以通过从中选择行来访问实际的计划。您只在时间表中进行更新和插入，以避免出现您所描述的问题

DELIMITER //
DROP PROCEDURE IF EXISTS procedure_to_run_schedule//

CREATE DEFINER=root@localhost PROCEDURE procedure_to_run_schedule()
READS SQL DATA
BEGIN

  SET SQL_SAFE_UPDATES = 0;
   DELETE FROM newschedule;
   INSERT INTO newschedule
     SELECT * FROM Schedule1;
    UPDATE newschedule t2
    Left JOIN newschedule t1 ON t2.Predecessor_activity   = t1.Id
   SET t2.start_date = IFNULL(t2.start_date,t1.Finish_date) ;
  SET SQL_SAFE_UPDATES = 1;
END//
DELIMITER