与将日期添加到中一样容易吗group by？以下是适用于MysqL和sqlite的语法，将日期基于结束时间并假定结束时间存储为日期时间：
SELECT ID, thedate, AVG(diff) AS average,
AVG(diff*diff) - AVG(diff)*AVG(diff) AS variance,
SQRT(AVG(diff*diff) - AVG(diff)*AVG(diff)) AS stdev,
MIN(diff) AS minTime,
MAX(diff) AS maxTime
FROM (SELECT t1.id, t1.endTimestamp, DATE(endtimestamp) as thedate,
min(t2.startTimeStamp) - t1.endTimestamp AS diff
FROM table1 t1 INNER JOIN
table1 t2
ON t2.ID = t1.ID AND t2.subject = t1.subject AND
t2.startTimestamp > t1.startTimestamp -- consider only later startTimestamps
WHERE t1.subject = 'entrance'
GROUP BY t1.id, t1.endTimestamp
) AS diffs
GROUP BY ID, thedate
如果存储为时间戳，请参阅Marty的注释。
602392714
清零编程群