给定此数据：
workercode | timestamp | action |
01 | 19/07/20 09:00:00 | _in |
01 | 19/07/20 16:00:00 | _out |
01 | 20/07/20 09:00:00 | _in |
01 | 20/07/20 13:00:00 | _out |
02 | 16/07/20 09:00:00 | _in |
02 | 16/07/20 15:00:00 | _out |
我需要为每个用户获取当天该用户停留的时间。就像是：
01 | 19/07/20 | 7 |
01 | 20/07/20 | 4 |
02 | 16/07/20 | 6 |
我正在尝试按分组，workercode并cast(timestamp, date)在选择上尝试类似datediff的方法，但我无法做到这一点。是否有捷径可寻？
提前致谢。
试试这个：
它用于MsSql。
// 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
它用于MySql。
//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;
但是此代码尚未进行数据检查。
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句