问mysql在行上选择grouping by和datdiff

EN

// For MS SQL.    
    CREATE TABLE TEST(workercode int, timestamp DATETIME2, action NVARCHAR(10))
    
    INSERT INTO dbo.TEST(workercode, timestamp, action) VALUES (1, '19/07/20 09:00:00', '_in')
    INSERT INTO dbo.TEST(workercode, timestamp, action) VALUES (1, '19/07/20 16:00:00', '_out')
    INSERT INTO dbo.TEST(workercode, timestamp, action) VALUES (1, '20/07/20 09:00:00', '_in')
    INSERT INTO dbo.TEST(workercode, timestamp, action) VALUES (1, '20/07/20 13:00:00', '_out')
    INSERT INTO dbo.TEST(workercode, timestamp, action) VALUES (2, '16/07/20 09:00:00', '_in')
    INSERT INTO dbo.TEST(workercode, timestamp, action) VALUES (2, '16/07/20 15:00:00', '_out')


SELECT temp.workercode, temp.date, DATEDIFF(HOUR, MIN(temp.timestamp), MAX(temp.timestamp)) 
    FROM (SELECT DATEFROMPARTS(Year(t.timestamp), Month(t.timestamp), Day(t.timestamp)) as date, t.workercode, t.timestamp, t.action  FROM dbo.TEST as t) as temp
GROUP BY temp.date, temp.workercode

//For MySQL
CREATE TABLE TEST(workercode int, timestamp1 TIMESTAMP, action VARCHAR(10));

INSERT INTO TEST(workercode, timestamp1, action) VALUES (1, "19/07/20 09:00:00", "_in");
INSERT INTO TEST(workercode, timestamp1, action) VALUES (1, "19/07/20 16:00:00", "_out");
INSERT INTO TEST(workercode, timestamp1, action) VALUES (1, "20/07/20 09:00:00", "_in");
INSERT INTO TEST(workercode, timestamp1, action) VALUES (1, "20/07/20 13:00:00", "_out");
INSERT INTO TEST(workercode, timestamp1, action) VALUES (2, "16/07/20 09:00:00", "_in");
INSERT INTO TEST(workercode, timestamp1, action) VALUES (2, "16/07/20 15:00:00", "_out");


SELECT temp.workercode, temp.date, TIMEDIFF(MAX(temp.timestamp1), MIN(temp.timestamp1)) 
    FROM (SELECT DATE(CONCAT_WS('-',Year(t.timestamp1), Month(t.timestamp1), Day(t.timestamp1))) as date, t.workercode, t.timestamp1, t.action  FROM TEST as t) as temp
GROUP BY temp.date, temp.workercode;