我正试图构造一个mysql查询来计算一个表,其中包含办公室人员的进出口。
我所拥有的:
id PERSON IN OUT
1 Person A 2019-03-11 08:59:30 NULL
2 Person B 2019-03-11 08:32:00 NULL
3 Person C 2019-03-11 08:04:40 NULL
4 Person D 2019-03-11 07:58:50 NULL
5 Person E 2019-03-11 07:35:20 NULL
6 Person F 2019-03-11 07:35:00 NULL
7 Person A NULL 2019-03-11 15:00:50
8 Person B NULL 2019-03-11 14:57:00
8 Person C NULL 2019-03-11 13:19:50
9 Person D NULL 2019-03-11 15:14:20
10 Person E NULL 2019-03-11 15:15:50
11 Person F NULL 2019-03-11 15:28:10
我想得到的是:
id PERSON IN OUT DIFF IN MINUTES
1 Person A 2019-03-11 08:59:30 2019-03-11 15:00:50 XXX
2 Person B 2019-03-11 08:32:00 2019-03-11 14:57:00 XXX
3 Person C 2019-03-11 08:04:40 2019-03-11 13:19:50 XXX
4 Person D 2019-03-11 07:58:50 2019-03-11 15:14:20 XXX
5 Person E 2019-03-11 07:35:20 2019-03-11 15:15:50 XXX
6 Person F 2019-03-11 07:35:00 2019-03-11 15:28:10 XXX
TOTAL OF XXXS
TOTAL OF XXXS - YYY (constant)
这样做的目的是得到一天内在办公室里所花时间的信息。此外,我需要每个人从整个月的会议纪要。每人/每月分组。
我花了一些时间使用这个查询,但效果平平:
SELECT a.PERSON, a.IN, b.OUT, TIMESTAMPDIFF(SECOND,a.IN,b.OUT)-28880 AS WHATSLEFT
FROM presence a
INNER JOIN presence b
ON a.PERSON = b.PERSON
WHERE DATEDIFF(a.IN,b.OUT) = 0 AND b.PERSON ="Person A"
ORDER BY a.IN;
谢谢你的帮助!
亚当