php - MySQL:从日期时间列中计算连续工作日的数量

Question

我有下表:

studentid VARCHAR(12)
latetime DATETIME
attendance CHAR(1)

最晚只有工作日。

有些日子，学生的出勤栏会出现用 V 表示的“家长信”。

我需要按连续工作日对这些出勤列 V 进行分组。然后计算这些出现的次数。每组连续天数计为 1 个字母。

我的 SQLFIDDLE:http://sqlfiddle.com/#!2/55d5b/1

此 SQLFIDDLE 示例数据应返回

STUDENTID   LETTERCOUNT
a1111           3
b2222           2


a1111 - 3 counts
-----
1. 2014-01-02
2. 2014-01-27
2. 2014-01-29 and 2014-01-30

b2222 - 2 counts
-----
1. 2014-01-02 and 2014-01-03
2. 2014-01-24, 2014-01-27 and 2014-01-28

我尝试了以下各种方法，但尚未得到任何正确的结果:

How to GROUP BY consecutive data (date in this case)

MySQL: group by consecutive days and count groups

我可以通过循环遍历结果并手动检查每条记录及其下一个日期，在 PHP 中以编程方式执行此操作。但我试图用 SQL 达到同样的效果。

任何寻找解决方案的帮助/指导将不胜感激。

Answer 1

这源自 MySQL: group by consecutive days and count groups 中的答案之一。我添加了 WITH ROLLUP 选项以将字母计数放入同一查询中，并使用 GROUP_CONCAT 显示所有日期。我在工作日设置了 INTERVAL 条件，以跳过周末；不过，没有考虑假期。

在我的 fiddle 版本中，我将 latetime 列更改为 date，这样我就可以从SQL。

SELECT studentid, IFNULL(dates, '') dates, IF(dates IS NULL, lettercount, '') lettercount
FROM (
    SELECT studentid, dates, COUNT(*) lettercount
    FROM (
        SELECT v.studentid,
          GROUP_CONCAT(latetime ORDER BY latetime SEPARATOR ', ') dates
        FROM
          (SELECT studentid, latetime,
                @start_date := IF(@last_student IS NULL OR @last_student <> studentid,
                         1,
                         IF(@last_latetime IS NULL
                            OR (latetime - INTERVAL IF(WEEKDAY(latetime) = 0, 3, 1) DAY) > @last_latetime, latetime, @start_date)) AS start_date,
                @last_latetime := latetime,
                @last_student := studentid
           FROM
             studentattendance, (SELECT @start_date := NULL, @last_latetime := NULL, @last_student := NULL) vars
           WHERE attendance = 'V'
           ORDER BY
             studentid, latetime
          ) v
        GROUP BY
          v.studentid, start_date) x
    GROUP BY studentid, dates WITH ROLLUP) y
WHERE studentid IS NOT NULL
ORDER BY studentid, dates

http://sqlfiddle.com/#!2/6c944/12