MySQL Workbench: Insert rows after checking data in 2 rows, loop on whole table

发布于 2021-03-06 17:19:30

I have a local MySQL Server instance and the MySQL Workbench for university topics.

Following table apjournal contains start and endtimes at workstations.

PAID is the production order,
ID the running number when the starttime was scanned

table apjournal

I would like to insert rows for the transportation/buffer time after each workstation.

Conditions:

(r1 := row i, r2 := row i+1)

WHERE PAID_from_r1 = PAID _from_r1 AND ID_from_r2 > ID_from_r1

THEN 
INSERT INTO sys.apjournal (ID, PAID, RID, APID, APID_ST, Startzeit, Endzeit, DurationEvent) 
VALUES (0, PAID_from_r1, RID_from_r1, APID_from_r1, CONCAT('Puffer ',APID_from_r1), Endzeit_from r1, Startzeit_from_r2, 0);

I searched for While Loop and procedures and Subqueries with running nummers. Here are my tries.

SELECT
    -
    @ID := CASE
        WHEN @last_ID > data.actual_ID AND @last_PAID = data.acutal_PAID
        THEN @ID + 1            
        ELSE 0
    END AS ID,
    @last_ID := data.ID,
    @last_PAID := data.PAID
    #INSERT INTO sys.apjournal (ID, PAID, RID, APID, APID_ST, Startzeit, Endzeit, DurationEvent) VALUES (3000+i, 1, 1)
    -- //TODO: Weitere Felder ausgeben
FROM
    -- Die Variablen initialisieren
    (SELECT @nr:= 0, @last_ID:=1,@last_PAID:=76) AS vars,
    -- Die Daten sortieren
    (
        SELECT t.*
        FROM sys.apjournal AS t
        ORDER BY PAID
    ) AS data;
    
    
#other strategy ?

drop procedure if exists addPuffer;

DELIMITER //  

CREATE PROCEDURE addPuffer(
StartID INT,
PAID Int
)   
BEGIN
DECLARE i INT DEFAULT 0; 
WHILE (i<2000)  DO
    INSERT INTO sys.apjournal (ID, PAID, RID, APID, APID_ST, Startzeit, Endzeit, DurationEvent) VALUES (3000+i, 1, 1);
    SET i = i+1;
END WHILE;
END;
//  
CALL addPuffer();

The problems of structuring the procedures, matching 2 rows are new for me. The procedure should loop over the whole table.

Questioner

ChrisB

Viewed

Chinese

Original

ChrisB 1970-01-01 08:00:00

One Problem is left. The Insert INTO does it twice, when there a for expample 2 workstation behind. How can I add sth. that it does the Insert into only one time per APID ? see the picture in your answer.

AND t1.ID < t2.ID : Here t2.Id should be the next bigger one. but Limit 1 ore DESC LIMIT 1 don't work.

AND t1.ID < t2.ID ORDER by PAID DESC Limit 1; doesn't work

I did it now with an help table with sequence order not depending on the APID: JOIN sys.apjournal t2 ON t1.PAID = t2.PAID AND t1.ap_sequence +1 = t2.ap_sequence;

MySQL Workbench: Insert rows after checking data in 2 rows, loop on whole table

Related issues