Elanochecer

对保罗每天提供的分钟数查询的改进是:

SELECT 
    a.PERSON, 
    a.IN, 
    b.OUT, 
    TIMESTAMPDIFF(MINUTE,a.IN,b.OUT) AS `DIFF IN MINUTES`
FROM presence a
INNER JOIN presence b ON ( a.PERSON = b.PERSON )
WHERE DAY(a.IN) = DAY(b.OUT)
ORDER BY a.IN;

DEMO DAY

对飞蛾的查询如下所示:

SELECT 
    a.PERSON, SUM( TIMESTAMPDIFF(MINUTE,a.IN,b.OUT) ) AS `MINUTES`
FROM presence a
INNER JOIN presence b ON ( a.PERSON = b.PERSON )
WHERE DAY(a.IN) = DAY(b.OUT)
AND a.PERSON = 'Person A'
AND a.IN BETWEEN '2019-03-01' AND '2019-04-01' 
AND b.OUT BETWEEN '2019-03-01' AND '2019-04-01' 
GROUP BY a.PERSON;

DEMO MONTH