如何从学校日期列表中找到累计缺席日期 [英] How to find a cumulative absent dates from a list of school dates

问题描述

我有代码可以从我们的上课日表中提取出勤"日期(消除假期、周末、研讨会日等).

I have code to pull the 'attendance' dates from our school days table (eliminating holidays, weekends, workshop days, etc.).

AttendanceDate
8/30/2017
8/31/2017
9/1/2017
9/5/2017
9/6/2017
9/7/2017
9/8/2017
9/11/2017
9/12/2017
9/13/2017
9/14/2017
9/15/2017
9/18/2017
9/19/2017
9/20/2017
9/21/2017
9/22/2017
9/25/2017
9/26/2017
9/27/2017
9/28/2017
9/29/2017
10/2/2017
10/3/2017

我有代码可以构建一个临时表，为我提供学生 ID 列表和他们缺席的日期.

I have code that builds a temp table to give me a list of student ids and the dates they have been absent.

studentID | currDate
89        | 9/18/2017
89        | 10/5/2017
89        | 10/16/2017
537       | 9/6/2017
541       | 9/11/2017
541       | 9/27/2017
549       | 9/18/2017
549       | 9/20/2017
549       | 9/28/2017
549       | 9/29/2017
549       | 10/2/2017
549       | 10/3/2017
549       | 10/4/2017
549       | 10/5/2017
549       | 10/10/2017
550       | 10/19/2017
845       | 9/26/2017
845       | 10/2/2017
897       | 10/5/2017
897       | 10/20/2017
990       | 9/22/2017
990       | 9/26/2017
990       | 9/27/2017
990       | 9/28/2017
990       | 10/3/2017

我想要为每个学生 ID 做的是将他们的缺勤日期与上学日表出勤日期进行比较，并找出累计缺勤.我一直在绞尽脑汁想看看如何做到这一点(临时表、游标等)，但一直无法弄清楚.

What I want to do is for each studentID is compare their absent dates against the school days table attendance dates and find the cumulative absences. I have been racking my brain to try to see how to do this (temp tables, cursors, etc.) but haven't been able to figure it out.

我通过 AquaData Studio 使用 MySQL 2012.有任何想法吗?最后，我想要一份报告，其中列出了累计缺勤次数超过 5 次的学生名单.谢谢！

I'm using MySQL 2012 via AquaData Studio. Any ideas? In the end, a report that gives me a list of students with over 5 cumulative absences is what I'm after. Thank you!

推荐答案

如果你想要五个缺席:

select s.studentId, count(*)
from tempstudents s
group by s.studentId
having count(*) >= 5;

但是，我怀疑您打算在 AttendanceDates 中连续 5 天.首先，添加一列枚举出勤日期:

However, I suspect that you intend 5 days in a row in AttendanceDates. First, add a column to enumerate the attendance dates:

alter table AttendanceDates add id int;

set @id = 0;
update AttendanceDates
    set id = (@id := @id + 1)
    order by AttendanceDate;

然后，您想连续查找五个 id.这是一种方法:

Then, you want to find five ids in a row. Here is one method:

select studentid, count(*), min(currdate), max(currdate)
from (select ads.*,
             (@rn := if(@s = studentid, @rn + 1, if(@s := studentid, 1, 1))) as rn
      from (select s.*, ad.id
            from AttendanceDates ad join
                 tempstudents s
                 on s.currdate = ad.AttendanceDate
            order by s.studentid, ad.id
           ) ads cross join
           (select @s := 0, @rn := 0) params
     ) ads
group by s.studentid, (id - @rn)
having count(*) >= 5;

这使用变量来枚举当前日期.然后它与 id 不同，后者标识缺席序列.外层聚合简单地计算这些得到 5.

This uses variables to enumerate the current dates. It then takes the difference from the id, which identifies sequences of absences. The outer aggregation simply counts these to get 5.

这篇关于如何从学校日期列表中找到累计缺席日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！